i'm trying to configure a rails app to remotely connect to a postgres db. i've noticed that the connection adapters for mysql have options that specify the required info for setting up an ssl connection, but there is no equivalent options for the postgres/pg adapter. after googling around, i haven't been able to find anything either (on...
I have a table defined like this: CREATE TABLE A ( begin date, end date, CONSTRAINT ordered_dates CHECK ( begin <= end) ) ..and 2 triggers associated : trigger1 on BEFORE update trigger2 on AFTER update In the trigger1, there's an insert in a second table B by computing the interval (=end-begin). If the constraint ordere...
I have an application where a user creates settings that are stored in an NSMutableDictionary. I have to send this to a server, where it can be later retrieved by a recipient. I can serialize the dictionary, and send it to the server. My question is, if I send it as NSData, store it in a blob field in my postgresql db, and then send it...
I have some queries being run in a java program that makes use of a PostgreSQL database and some parts of an old version of JDataStore (the parts used to interact with the database). Sometimes the queries are sent to the database twice from one execution of a query. What's even more odd is that the first query sent is slightly different ...
Hello. I'm on Mac OSX 10.5.8. I have followed Jacob Kaplan-Moss's article on setting up Django with Buildout: http://jacobian.org/writing/django-apps-with-buildout/ Finally, I have got this Buildout to work! ...but I'm now needing PIL and Postgres for a complete isolated Django development area. I've tried to modify my buildout.cfg with...
We had a database spin itself out of control and fill the SAN partition it was sharing with other services. In some other RDBMSen, there are ways to disable autogrowth. I've not found that approach (yet) with Postgres. Putting aside the problem database that caused this mess... What is your approach to preventing or limiting table/da...
I run a moderately popular web app on Django with Apache2, mod_python, and PostgreSQL 8.3 with the postgresql_psycopg2 database backend. I'm experiencing occasional livelock, identifiable when an apache2 process continually consumes 99% of CPU for several minutes or more. I did an strace -ppid on the apache2 process, and found that it w...
I'm trying to startup my PostgreSQL server on my local machine. But I got an error message saying: FATAL: could not create shared memory segment: Invalid argument DETAIL: Failed system call was shmget(key=5432001, size=9781248, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded you...
I have a web application written in Perl using PostgreSQL. When accessing the PostgreSQL database I need to supply both username and password. In order to have the password available for unattended start-ups of the system I need have that password embedded in my application or in a configuration file or as an environment variable confi...
In a web application written in Perl and using PostgreSQL the users have username and password. What would be the recommended way to store the passwords? Encrypting them using the crypt() function of Perl and a random salt? That would limit the useful length of passswords to 8 characters and will require fetching the stored password in ...
My question is kind of simple. Is it possible to use subqueries within alter expressions in PostgreSQL? I want to alter a sequence value based on a primary key column value. I tried using the following expression, but it wouldn't execute. alter sequence public.sequenceX restart with (select max(table_id)+1 from table) Thanks in ...
i have a postgres database with millions of rows in it it has a column called geom which contains the boundary of a property. using a python script i am extracting the information from this table and re-inserting it into a new table. when i insert in the new table the script bugs out with the following: Traceback (most recent call las...
Hi. I have a large table (2,000,000 rows) and I'd like to print each record to the screen, one at time, without loading the entire table into memory. //pseudo code var cmd = new NpgSQLCommand(); cmd.CommandText = "SELECT * FROM mytable;" IReader reader = cmd.ExecuteReader(); //blocks until the entire set is returned while(reader.Read(...
Hi I have a Product table and a ConfigurableProduct table. If there are several variations of the same product like a shirt in different colors I create a ConfigurableProduct. When a user is looking at the catalog he should see a list of products unless there is a ConfigurableProduct, then he should see it with a select box for each va...
Is there a way in postgresql to have an auto-incrementing column reset back to zero at a specified time every day? ...
I'm working on a website with typical CRUD web usage pattern: similar to blogs or forums where users create/update contents and other users read the content. Seems like it's OK to set the database's isolation level to "Read Uncommitted" (dirty reads) in this case. My understanding of the general drawback of "Read Uncommitted" is that a ...
Hello there, Two questions: i want to generate a View in my PostGIS-DB. How do i add this View to my geometry_columns Table? What i have to do, to use a View with SQLAlchemy? Is there a difference between a Table and View to SQLAlchemy or could i use the same way to use a View as i do to use a Table? sorry for my poor english. If t...
Is it possible with Postgresql to create a database which has 2 users which act like owners to the database? I can create a group role and add both users to that group, and then make the group the owner of the database, but this requires both users to be have to manually set their role on every connection to make any tables they have cr...
Hi in the pre-8.x days I have run numerous PGSQL databases on Linux, and did the database vacuuming explicitly via maintenance script. When 8.x came along I did not move to autovacuum on those systems on the basis that it was working fine so no need to change anything. So I am not an autovacuum expert. Now in the post-8.x era I find m...
I have database where one column contains numbers. 3 example values: 1111111555 2222222555 3333333555 which I need to reverse and put a dot between each digit. i.e. the result for each of the examples above would be: 5.5.5.1.1.1.1.1.1.1 5.5.5.2.2.2.2.2.2.2 5.5.5.3.3.3.3.3.3.3 respectively. I then need to update another column wi...