Re: [GENERAL] postgresql server crash on windows 7 when using plpython
Yes, Now I have removed python 2.7. Restarted the postgresql. When I call the above mentioned function, now server doesn't crash. It is running, but I get the error 'No connection to the server'. I am using PgAdmin to work with Postgresql. I tried to execute a simple function to show python version or path, but still I am getting the same error. I tried to execute other plpython function but still the same problem. I can use other plsql and sql functions correctly. When checked the log file it have entries like this: 2011-08-24 11:19:57 IST LOG: database system was interrupted; last known up at 2011-08-10 22:25:38 IST 2011-08-24 11:19:57 IST LOG: database system was not properly shut down; automatic recovery in progress 2011-08-24 11:19:57 IST FATAL: the database system is starting up 2011-08-24 11:19:57 IST LOG: consistent recovery state reached at 0/424E9800 2011-08-24 11:19:57 IST LOG: redo starts at 0/424E9800 2011-08-24 11:19:57 IST LOG: record with zero length at 0/424EFC70 2011-08-24 11:19:57 IST LOG: redo done at 0/424EFC30 2011-08-24 11:19:57 IST LOG: last completed transaction was at log time 2011-08-10 22:27:35.06+05:30 2011-08-24 11:19:57 IST LOG: database system is ready to accept connections 2011-08-24 11:19:58 IST LOG: autovacuum launcher started ImportError: No module named site ImportError: No module named site ImportError: No module named site ImportError: No module named site ImportError: No module named site ImportError: No module named site ImportError: No module named site This last line is added every time I call any plpython function. Here is the simple plpython function. CREATE OR REPLACE FUNCTION software.pyver() RETURNS text AS $BODY$ import sys #return sys.version return sys.path $BODY$ LANGUAGE plpythonu VOLATILE COST 100; What is the problem? Chaitany Kulkarni On Mon, Aug 22, 2011 at 8:08 PM, Adrian Klaver adrian.kla...@gmail.comwrote: On Monday, August 22, 2011 7:24:30 am c k wrote: No i didn't removed any thing. Only I have installed python 2.6. And then tried to create plpythonu. It got created, but when tries to execute already mentioned function server crashes. I didn't have any clue. My guess is this is the clue: First I installed python 2.7 and then added python installation path to windows PATH variable. ... It is very possible you have a version cross reference going on. In other words pl/pythonu is being compiled against one version of Python, but run against another. If it is possible I would remove the Python 2.7 installation or at least the references to it and then recompile pl/pythonu. Thanks and regards. Chaitany Kulkarni -- Adrian Klaver adrian.kla...@gmail.com
Re: [GENERAL] JDBC Connection Errors
On Tue, 2011-08-23 at 19:38 -0400, Dave Cramer wrote: The only difference JDBC has over psql is that it has to connect via tcpip. Not sure about pgadmin. pgAdmin is like psql on this. It can use TCP connections, and socket connections. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Collapsing multiple subqueries into one
Thanks Royce. I put together another query using a WITH statement that's also working: WITH v AS ( SELECT item_id, type, direction, array_agg(user_id) as user_ids FROM votes WHERE root_id = 5305 GROUP BY type, direction, item_id ORDER BY type, direction, item_id ) SELECT *, (SELECT user_ids from v where item_id = i.id AND type = 0 AND direction = 1) as upvoters, (SELECT user_ids from v where item_id = i.id AND type = 0 AND direction = -1) as downvoters, (SELECT user_ids from v where item_id = i.id AND type = 1) as favoriters FROM items i WHERE root_id = 5305 ORDER BY id It feels more sensible to me, but it's slightly slower than my initial attempt (15 ms vs. 13 ms, when running as a prepared statement to avoid any query parsing overhead, and averaging the time over several thousand queries). I'm not sure why...? On Tue, Aug 23, 2011 at 8:14 PM, Royce Ausburn royce...@inomial.com wrote: This might help you: http://www.postgresql.org/docs/8.4/static/queries-with.html On 24/08/2011, at 9:54 AM, Chris Hanks wrote: I have two tables: CREATE TABLE items ( root_id integer NOT NULL, id serial NOT NULL, -- Other fields... CONSTRAINT items_pkey PRIMARY KEY (root_id, id) ) CREATE TABLE votes ( root_id integer NOT NULL, item_id integer NOT NULL, user_id integer NOT NULL, type smallint NOT NULL, direction smallint, CONSTRAINT votes_pkey PRIMARY KEY (root_id, item_id, user_id, type), CONSTRAINT votes_root_id_fkey FOREIGN KEY (root_id, item_id) REFERENCES items (root_id, id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, -- Other constraints... ) I'm trying to, in a single query, pull out all items of a particular root_id along with a few arrays of user_ids of the users who voted in particular ways. The following query does what I need: SELECT *, ARRAY(SELECT user_id from votes where root_id = i.root_id AND item_id = i.id AND type = 0 AND direction = 1) as upvoters, ARRAY(SELECT user_id from votes where root_id = i.root_id AND item_id = i.id AND type = 0 AND direction = -1) as downvoters, ARRAY(SELECT user_id from votes where root_id = i.root_id AND item_id = i.id AND type = 1) as favoriters FROM items i WHERE root_id = 1 ORDER BY id The problem is that I'm using three subqueries to get the information I need when it seems like I should be able to do the same in one. I thought that Postgres (I'm using 8.4) might be smart enough to collapse them all into a single query for me, but looking at the explain output in pgAdmin it looks like that's not happening - it's running multiple primary key lookups on the votes table instead. I feel like I could rework this query to be more efficient, but I'm not sure how. Any pointers? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JDBC Connection Errors
Hi, - check for open server socket: netstat -tulpen | grep postgres - try to force ipv4 for java with system property (a recent jre prefers ipv6): -Djava.net.preferIPv4Stack=true regards Thomas Am 24.08.2011 00:47, schrieb Sam Nelson: Hi list, A client is hitting an issue with JDBC: org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. -pg_hba.conf is set to trust 0.0.0.0/0 (IPv4 only) -listen_addresses is * -I can find no evidence of iptables running on the server. -PGAdmin connects just fine. -psql connects just fine. -I can find no errors in the log file from that day for the user that the client is trying to log in as. We're working on getting access to more details about how they're trying to connect, but in the mean time, does anyone know if JDBC has any issues connecting that psql and PGAdmin wouldn't have? Is it possible that JDBC is somehow susceptible to connection issues that JDBC and psql are not? --- === Samuel Nelson Consistent State www.consistentstate.com 303-955-0509 === -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection Error during Pg_restore
That worked thank you On Wed, Aug 17, 2011 at 1:01 PM, raghu ram raghuchenn...@gmail.com wrote: On Wed, Aug 17, 2011 at 4:32 PM, Rebecca Clarke rebe...@clarke.net.nzwrote: Hi there I'm doing a restore of a large table. The backup file is 18gb. When I run the restore after sometime it comes up with this error while it is restoring the data. pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3022; 0 4287406059 TABLE DATA tbl_exampletable postgres pg_restore: [archiver (db)] COPY failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. WARNING: errors ignored on restore: 1 It will work if I use COPY but the table is so big I need to use pg_dump to compress it. Try below steps to restore data for larger table: 1. Create a table structure 2. copy data into .csv file copy item_trans to '/opt/item_trans.csv' WITH csv; 3. restore data with below command nohup psql -d postgres -p 5432 -U postgres -c copy item_trans from '/opt/item_trans.csv' WITH csv; /tmp/restore.log 2/tmp/restore.log --Raghu Ram
Re: [GENERAL] date_trunc - not sure what is wrong, but it confuses me.
thanks. I hadn't used psql before, PgAdmin had been all I needed. You are right, \df does give the full info, and I do believe the manual isn't clear enough. TVM David -- View this message in context: http://postgresql.1045698.n5.nabble.com/date-trunc-not-sure-what-is-wrong-but-it-confuses-me-tp4716052p4729924.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql server crash on windows 7 when using plpython
On Tuesday, August 23, 2011 11:10:19 pm c k wrote: Yes, ImportError: No module named site ImportError: No module named site ImportError: No module named site This last line is added every time I call any plpython function. Here is the simple plpython function. CREATE OR REPLACE FUNCTION software.pyver() RETURNS text AS $BODY$ import sys #return sys.version return sys.path $BODY$ LANGUAGE plpythonu VOLATILE COST 100; What is the problem? plpythonu cannot find the Python installation. site is a module imported by default by the Python interpreter. When you removed 2.7 you probably also removed the PATH entries for Python. So to recap, previously you where running plpythonu compiled with 2.6 using a PATH leading to 2.7, hence the crashes. Now you have 2.7 out of the way, but no PATH to 2.6. Chaitany Kulkarni -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] strange exclusive lock in relation --- ideas
hello all, i came with a strange finding the other day and i would appreciate any ideas on the matter (if any). while checking on the locks of the server i found a tuple indicating that a prepared transaction had requested an exclusive lock on a relation. in general, i am aware of the situations where an exclusive lock is requested (alter table, drop, create index, vacuum full ...) but i would like to know if there are any other cases when an exclusive lock request could be issued. i have ran many tests since then but didnt manage to recreate the lock. i used various combinations of concurrent transactions of inserts,update,deletes using foreign keys and other constraints but with no use. so, do you have in mind any other situation (beside the obvious ones mentioned before) when such a lock can be requested? thx in advance -- View this message in context: http://postgresql.1045698.n5.nabble.com/strange-exclusive-lock-in-relation-ideas-tp4730583p4730583.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] init script or procedure
Does PG support the use of an init script or procedure? I'm looking for something that'll run unconditionally every time someone makes a DB connection. This script will create a temp table and stuff some data in it for general use within that session. Thanks in Advance for any help!
Re: [GENERAL] init script or procedure
Hello 2011/8/24 Gauthier, Dave dave.gauth...@intel.com: Does PG support the use of an init script or procedure? I'm looking for something that'll run unconditionally every time someone makes a DB connection. This script will create a temp table and stuff some data in it for general use within that session. no, there is nothing similar. You have to call a own procedure after login explicitly Regards Pavel Stehule Thanks in Advance for any help! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] init script or procedure
On Wed, Aug 24, 2011 at 9:30 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello 2011/8/24 Gauthier, Dave dave.gauth...@intel.com: Does PG support the use of an init script or procedure? I'm looking for something that'll run unconditionally every time someone makes a DB connection. This script will create a temp table and stuff some data in it for general use within that session. no, there is nothing similar. You have to call a own procedure after login explicitly We could really use this. It's kinda sorta possible to script SQL on connection close via C hook through dblink but not on session startup. It's not always possible for the client know when a backend session is fired up -- for example in connection pools. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] strange exclusive lock in relation --- ideas
On Wed, Aug 24, 2011 at 9:11 AM, MirrorX mirr...@gmail.com wrote: hello all, i came with a strange finding the other day and i would appreciate any ideas on the matter (if any). while checking on the locks of the server i found a tuple indicating that a prepared transaction had requested an exclusive lock on a relation. in general, i am aware of the situations where an exclusive lock is requested (alter table, drop, create index, vacuum full ...) but i would like to know if there are any other cases when an exclusive lock request could be issued. i have ran many tests since then but didnt manage to recreate the lock. i used various combinations of concurrent transactions of inserts,update,deletes using foreign keys and other constraints but with no use. so, do you have in mind any other situation (beside the obvious ones mentioned before) when such a lock can be requested? thx in advance a couple more 'obvious' ones: LOCK CLUSTER TRUNCATE REINDEX hm, can't think of anything else off top of head... merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] init script or procedure
2011/8/24 Merlin Moncure mmonc...@gmail.com: On Wed, Aug 24, 2011 at 9:30 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello 2011/8/24 Gauthier, Dave dave.gauth...@intel.com: Does PG support the use of an init script or procedure? I'm looking for something that'll run unconditionally every time someone makes a DB connection. This script will create a temp table and stuff some data in it for general use within that session. no, there is nothing similar. You have to call a own procedure after login explicitly We could really use this. It's kinda sorta possible to script SQL on connection close via C hook through dblink but not on session startup. It's not always possible for the client know when a backend session is fired up -- for example in connection pools. connection pooling is problem every time. This is not task for server, but for connection pooling maintainer. Once we used a workaround - on a start every function we tested if session is well initialized, and if not, then we called a init function. But it has a problem with pooling, so we changed to explicitly call a init function. Regards Pavel Stehule merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] init script or procedure
On Wed, Aug 24, 2011 at 9:51 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2011/8/24 Merlin Moncure mmonc...@gmail.com: On Wed, Aug 24, 2011 at 9:30 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello 2011/8/24 Gauthier, Dave dave.gauth...@intel.com: Does PG support the use of an init script or procedure? I'm looking for something that'll run unconditionally every time someone makes a DB connection. This script will create a temp table and stuff some data in it for general use within that session. no, there is nothing similar. You have to call a own procedure after login explicitly We could really use this. It's kinda sorta possible to script SQL on connection close via C hook through dblink but not on session startup. It's not always possible for the client know when a backend session is fired up -- for example in connection pools. [snip] . This is not task for server, I disagree. Other databases have them (see http://msdn.microsoft.com/en-us/library/bb326598.aspx), and they are highly used and useful. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] init script or procedure
2011/8/24 Merlin Moncure mmonc...@gmail.com: On Wed, Aug 24, 2011 at 9:51 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2011/8/24 Merlin Moncure mmonc...@gmail.com: On Wed, Aug 24, 2011 at 9:30 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello 2011/8/24 Gauthier, Dave dave.gauth...@intel.com: Does PG support the use of an init script or procedure? I'm looking for something that'll run unconditionally every time someone makes a DB connection. This script will create a temp table and stuff some data in it for general use within that session. no, there is nothing similar. You have to call a own procedure after login explicitly We could really use this. It's kinda sorta possible to script SQL on connection close via C hook through dblink but not on session startup. It's not always possible for the client know when a backend session is fired up -- for example in connection pools. [snip] . This is not task for server, I disagree. Other databases have them (see http://msdn.microsoft.com/en-us/library/bb326598.aspx), and they are highly used and useful. other databases has own integrated connection pooling I am not absolutely against - but we need a integrated pool before Pavel merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] question regarding full_page_write
El día 22 de agosto de 2011 18:39, Greg Smith g...@2ndquadrant.com escribió: On 08/22/2011 05:07 PM, Martín Marqués wrote: My question regarding your answer is, why is it important for the first page after a checkpoint and not on other page writes? The first time a page is written after a checkpoint, when full_page_writes is on, the entire 8K page is written out to disk at that point. The idea is that if the page is corrupted in any way by a partial write, you can restore it to a known good state again by using this version. After that copy, though, additional modifications to the page only need to save the delta of what changed, at the row level. If there's a crash, during recovery the full page image will be written, then the series of deltas, ending up with the same data as was intended. This whole mechanism resets again each time a checkpoint finishes, and the full page writes start all over again. One of the main purposes of checkpoints are to move forward the pointer of how far back crash recovery needs to replay from. Starting each new checkpoint over again, with a full copy of all the data modified going into the WAL, it is part of that logic. Still something missing (for me :-)): Checkpoint happens, logs get flushed to disk, WAL segments get archived if archive is on, and then the are recycled. Now a new transaction get commited, and at least 1 page has to go to WAL (those are 8Kb). full_page_writes garantees all 8Kb are written or nothing. After this thansaction comes another, but here you don't need the garantee of all 8Kb in WAL (you say because deltas are enough). Why aren't deltas good enough for the first 8Kb? Is there other information in the first 8Kb that make those more important? -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Streaming Replication: Observations, Questions and Comments
Hi all, We have a postgres-9.0 streaming replication set up where we keep the WAL segments on the master amounting to 10 GB so that we can survive longer periods of disconnect between master and slave. We do not use any shared storage space for archiving WAL logs. (the shared disk server may turn out to be another point of failure, which we would want to avoid) Here is our basic configuration parameters in : postgresql.conf on master: wal_keep_segments = 640 # previously 32 | # in logfile segments, min 1, 16MB each archive_mode = on # allows archiving to be done # (change requires restart) archive_command = 'cp -v %p /archives/data/pgsql/pg_xlog/%f' postgresql.conf on slave: wal_level = hot_standby hot_standby = on recovery.conf on slave: standby_mode = 'on' primary_conninfo = 'host=ip.add.ress port=5432 user=repman' trigger_file = '/var/lib/pgsql/data/stop.replication' restore_command = 'cp -i /archives/data/pgsql/pg_xlog/%f %p' Master and Slave servers are sperated by thousands of miles and the network bandwidth comprises just an ordinary 1 Mbps DSL line. Both the master server and the slave server have the /archives partition mounted and synced with csync2 between master and the slave systems. I'm not sure if this is the correct way of configuring streaming replication, but I will explain what worked for us and what we are still left wanting with: Under heavy inserts/updates/deletes on the master (load generated by stored procedures), we noticed that the slave went far behind the master and resulted into breakage of replication. Hence we changed from 32 log file segments to 640, which corresponds to 10 GB so that we can survive either very heavy spikes of load or even a week's disconnect of the slave (although alarms would be raised appropriately for the same effect). One strange thing I noticed is that the pg_xlogs on the master have outsized the actual data stored in the database by at least 3-4 times, which was quite surprising. I'm not sure if 'restore_command' has anything to do with it. I did not understand why transaction logs would need to be so many times larger than the actual size of the database, have I done something wrong somewhere? Another interesting fact we noticed is that once the replication is broken for some longer time ( walsender and walreceiver processes have died by this time), we had to restart not only the slave server but also the master server, which was quite strange. Shouldn't the master server start (if there is none running) walsender process the moment it receives a request for streaming? Similarly, why should the slave be restarted just to start replication again? why can't these two processes be independently started and stopped by the postmaster process as and when necessary as per the need to replicate or not? Another thing that I noticed was that the slave server has logged that it is out of sync and hence closing replication but the master did not say anything about this breakage of replication. So summing up the above, I would like to have some pointers to understand the following, which I think will benefit many others as well: - Do I need to 'archive' since I'm storing quite a significant number of logfile segments that can help sustain disconnect for almost a week? - Why did the xlog data accumulate so much as to be 3-4 times that of the actual size of the database growth during the same period? - why should we restart the postgres service on slave and/or master if we need to join a slave back into replication after a long disconnect? - why is the master not complaining about the loss of replication? (of course, slave did complain about the disconnect or its inability to continue accepting data from master on account difference in xlog location). Some of the above might be because of wrong configuration, while some may be give hints for future enhancements. I hope this will start a healthy discussion on the areas where streaming replication needs to be improved and strengthened. Thanks and Regards, Samba
Re: [GENERAL] init script or procedure
On Wed, Aug 24, 2011 at 10:06 AM, Pavel Stehule pavel.steh...@gmail.com wrote: [snip] . This is not task for server, I disagree. Other databases have them (see http://msdn.microsoft.com/en-us/library/bb326598.aspx), and they are highly used and useful. other databases has own integrated connection pooling I am not absolutely against - but we need a integrated pool before not to argue the point, but connection pooling is only one reason of many why you would want a logon trigger, and integrated connection pooling is not a prerequisite for them being implemented IMO. Login triggers are useful any time the client code is not completely under your control, for example when logging in with psql/pgadmin or any time really when the actionable item is required from the servers' point of view. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Explain Analyze understanding
Hi, I need to improve performance for a particular SQL command but facing difficulties to understand the explain results. Is there somewhere a tool could help on this? I've stored the SQL code and corresponding explain analyze at SQL: http://www.opendb.com.br/v1/sql.txt Explain: http://www.opendb.com.br/v1/explain.txt in case some one could identify some improvement that could be done to the SQL or table structures in order to get better command performance. Thank you! -- Reimer 47-3347-1724 47-9183-0547 msn: carlos.rei...@opendb.com.br
Re: [GENERAL] question regarding full_page_write
On 08/24/2011 11:12 AM, Martín Marqués wrote: Why aren't deltas good enough for the first 8Kb? Is there other information in the first 8Kb that make those more important? The fundamental problem is what's called a torn page. You write out a 8K page; only part of it actually makes it to disk; the server crashes. What you now have on disk is completely unpredictable. It's a mix of the old new page, but what portion of each you got, that's almost random. You cannot fix it with any delta. The only way to make sure it's back into a good state is to write an entire 8K page, a known correct copy, from some point in time. The way this is done in PostgreSQL, one of those is written out to the WAL the first time any page is touched after a checkpoint. Those become the known copy good to recover from any torn page problem. Then database recovery only has to replay activity since that checkpoint marker to fix all torn pages. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] init script or procedure
No, that's not possible. Zitat von Gauthier, Dave dave.gauth...@intel.com: Does PG support the use of an init script or procedure? I'm looking for something that'll run unconditionally every time someone makes a DB connection. This script will create a temp table and stuff some data in it for general use within that session. Thanks in Advance for any help! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plperlu function caused a segmentation fault
I was just testing a simple plperlu function to return the existence
of a directory and as soon I pasted the function into psql and hit
Enter, I got the following crash:
CREATE OR REPLACE FUNCTION does_directory_exist(p_path_and_directory
TEXT, OUT does_it_exist TEXT)
RETURNS TEXT AS
$BODY$
my $path_and_directory = shift(@_);
my $does_it_exist = 'false';
if ( -d $path_and_directory ) {
$does_it_exist = true;
}
return $does_it_exist;
$BODY$ LANGUAGE plperlu VOLATILE COST 100 SECURITY DEFINER;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Excerpt from the log
2011-08-24 08:55:57 PDT [25795]: [448-1] (user=) (rhost=) LOG: server
process (PID 9474) was terminated by signal 11: Segmentation fault
2011-08-24 08:55:57 PDT [25795]: [449-1] (user=) (rhost=) LOG:
terminating any other active server processes
2011-08-24 08:55:57 PDT [10309]: [21-1] (user=nbaffnet)
(rhost=192.168.96.183) WARNING: terminating connection because of
crash of another server process
2011-08-24 08:55:57 PDT [10309]: [22-1] (user=nbaffnet)
(rhost=192.168.96.183) DETAIL: The postmaster has commanded this
server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2011-08-24 08:55:57 PDT [10309]: [23-1] (user=nbaffnet)
(rhost=192.168.96.183) HINT: In a moment you should be able to
reconnect to the database and repeat your command.
...
2011-08-24 08:55:58 PDT [25795]: [451-1] (user=) (rhost=) LOG: all
server processes terminated; reinitializing
2011-08-24 08:56:00 PDT [14436]: [1-1] (user=nbaffnet)
(rhost=192.168.96.202) FATAL: the database system is in recovery mode
2011-08-24 08:56:00 PDT [14437]: [1-1] (user=postgres) (rhost=[local])
FATAL: the database system is in recovery mode
2011-08-24 08:56:00 PDT [14441]: [1-1] (user=nbaffnet)
(rhost=192.168.96.201) FATAL: the database system is in recovery mode
2011-08-24 08:56:00 PDT [14447]: [1-1] (user=nbaffnet)
(rhost=192.168.96.200) FATAL: the database system is in recovery mode
2011-08-24 08:56:00 PDT [14446]: [1-1] (user=nbaffnet)
(rhost=192.168.96.200) FATAL: the database system is in recovery mode
2011-08-24 08:56:00 PDT [14435]: [1-1] (user=) (rhost=) LOG: database
system was interrupted; last known up at 2011-08-24 08:55:13 PDT
2011-08-24 08:56:00 PDT [14435]: [2-1] (user=) (rhost=) LOG: database
system was not properly shut down; automatic recovery in progress
2011-08-24 08:56:00 PDT [14435]: [3-1] (user=) (rhost=) LOG:
consistent recovery state reached at 3A3B/CCFBD7C8
2011-08-24 08:56:00 PDT [14435]: [4-1] (user=) (rhost=) LOG: redo
starts at 3A3B/C7321A10
...
select * from pg_language ;
lanname | lanowner | lanispl | lanpltrusted | lanplcallfoid |
laninline | lanvalidator | lanacl
--+--+-+--+---+---+--+
internal | 10 | f | f| 0 |
0 | 2246 |
c| 10 | f | f| 0 |
0 | 2247 |
sql | 10 | f | t| 0 |
0 | 2248 |
plperl | 10 | t | t| 16545 |
16546 |16547 |
plperlu | 10 | t | f| 16545 |
16546 |16547 |
plpgsql | 10 | t | t| 11571 |
11572 |11573 |
(6 rows)
select version();
version
---
PostgreSQL 9.0.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit
Are there any known bugs with plperlu and 9.0.2? I did a quick search
of the archives and the only somewhat-relevant report I could find was
http://markmail.org/message/iib5eu7qgtazaorg
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Streaming Replication: Observations, Questions and Comments
On 08/24/2011 11:33 AM, Samba wrote: One strange thing I noticed is that the pg_xlogs on the master have outsized the actual data stored in the database by at least 3-4 times, which was quite surprising. I'm not sure if 'restore_command' has anything to do with it. I did not understand why transaction logs would need to be so many times larger than the actual size of the database, have I done something wrong somewhere? That's common to see. Systems that regularly UPDATE the same rows often can easily end up with a WAL stream much larger than the database. The WAL data contains enough information to replay every point in time from the base backup until the current time. That can be significantly larger than the database, which just holds the latest copy of the data. One of the biggest things that makes your WAL large are the full page writes that protect against incomplete writes. See question regarding full_page_writes thread happening on this list recently for details. Each time you touch a page, per checkpoint, another full copy of that page is written out. What I have to do in a lot of cases is significantly decrease the number of checkpoints in order to keep this overhead under control. The default config has a checkpoint every checkpoint_segments of work, and every checkpoint_timeout of time. That makes for a checkpoint every 5 minutes, and even more often under heavy load. If you increase checkpoint_segments a whole lot, all of your checkpoints will be based on the timeout instead. Then you can see how WAL load decreases as you increase checkpoint_timeout. I've had to set checkpoint_timeout as high as 30 minutes before on busy systems, to lower the WAL overhead. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
[GENERAL] Feature Request: DDL + RegExp - definitions
It'd be nice if the catalog entries that store the DDL constraints definitions and view definitions with regular expressions strings would preserved any escape string syntax (i.e.E) for RegExp backslashes. For example, PGAdmin uses these catalog entries to produce Create or Replace scripts to that allow one to alter table or view definitions. However, when certain regular expressions syntax are added to these definitions, the resulting scripts will not commit. The following email illustrates the behavior that is undesirable (to me). http://www.mail-archive.com/pgadmin-support@postgresql.org/msg11482.html -- Regards, Richard Broersma Jr. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Explain Analyze understanding
On 08/24/11 9:18 AM, Carlos Henrique Reimer wrote: I need to improve performance for a particular SQL command but facing difficulties to understand the explain results. you're joining like 50 tables in nested selects, getting 200 rows, and its only taking 3.5 seconds? where's the problem? you can paste EXPLAIN output into http://explain.depesz.com and get output like http://explain.depesz.com/s/hfWp which helps format it for better understanding. in this case, most of the time (3.35s worth) appears to be spent on sorts and an nidex scan inside an inner merge join thats processing 77000 rows. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql server crash on windows 7 when using plpython
No, I have added the python directory in the PATH. Another thing is I created another language plpython2u and succeeded. Still I will try to figure out the problem. Regards, Chaitanya Kulkarni On Wed, Aug 24, 2011 at 7:11 PM, Adrian Klaver adrian.kla...@gmail.comwrote: On Tuesday, August 23, 2011 11:10:19 pm c k wrote: Yes, ImportError: No module named site ImportError: No module named site ImportError: No module named site This last line is added every time I call any plpython function. Here is the simple plpython function. CREATE OR REPLACE FUNCTION software.pyver() RETURNS text AS $BODY$ import sys #return sys.version return sys.path $BODY$ LANGUAGE plpythonu VOLATILE COST 100; What is the problem? plpythonu cannot find the Python installation. site is a module imported by default by the Python interpreter. When you removed 2.7 you probably also removed the PATH entries for Python. So to recap, previously you where running plpythonu compiled with 2.6 using a PATH leading to 2.7, hence the crashes. Now you have 2.7 out of the way, but no PATH to 2.6. Chaitany Kulkarni -- Adrian Klaver adrian.kla...@gmail.com
Re: [GENERAL] Feature Request: DDL + RegExp - definitions
On Wed, Aug 24, 2011 at 11:30 AM, Richard Broersma richard.broer...@gmail.com wrote: It'd be nice if the catalog entries that store the DDL constraints definitions and view definitions with regular expressions strings would preserved any escape string syntax (i.e.E) for RegExp backslashes. For example, PGAdmin uses these catalog entries to produce Create or Replace scripts to that allow one to alter table or view definitions. However, when certain regular expressions syntax are added to these definitions, the resulting scripts will not commit. The following email illustrates the behavior that is undesirable (to me). http://www.mail-archive.com/pgadmin-support@postgresql.org/msg11482.html this is IMO not a feature, but a bug. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql server crash on windows 7 when using plpython
On 08/24/11 10:06 AM, c k wrote: I have added the python directory in the PATH. Another thing is I created another language plpython2u and succeeded. Still I will try to figure out the problem. is it in the PATH that the server is using? the server doesn't know or care anything about the client's PATH. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature Request: DDL + RegExp - definitions
On Wed, Aug 24, 2011 at 10:14 AM, Merlin Moncure mmonc...@gmail.com wrote: this is IMO not a feature, but a bug. Would the feature I'm requesting be a bug or is it the current behavior that would be considered a bug? -- Regards, Richard Broersma Jr. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql server crash on windows 7 when using plpython
Server and client both are a single machine. Chaitanya Kulkarni On Wed, Aug 24, 2011 at 10:47 PM, John R Pierce pie...@hogranch.com wrote: On 08/24/11 10:06 AM, c k wrote: I have added the python directory in the PATH. Another thing is I created another language plpython2u and succeeded. Still I will try to figure out the problem. is it in the PATH that the server is using? the server doesn't know or care anything about the client's PATH. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Streaming Replication: Observations, Questions and Comments
On August 24, 2011 08:33:17 AM Samba wrote: One strange thing I noticed is that the pg_xlogs on the master have outsized the actual data stored in the database by at least 3-4 times, which was quite surprising. I'm not sure if 'restore_command' has anything to do with it. I did not understand why transaction logs would need to be so many times larger than the actual size of the database, have I done something wrong somewhere? If you archive them instead of keeping them in pg_xlog, you can gzip them. They compress reasonably well. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql server crash on windows 7 when using plpython
On 08/24/11 10:33 AM, c k wrote: Server and client both are a single machine. that doesn't answer my question. PATH, like other environment variables, is specific to the process. just because you change the default path of your login account via $HOME/.profile or whatever, doesn't have any effect on a service daemon like postgresql which is running in an entirely seperate process context. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OLD. || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)
01.05.2011 12:58, Basil Bourque wrote: Hoorah! I was able to complete my single PL/pgSQL function to create history records tracking individual field value changes generically for all my tables. Some developers call this an audit trail, though an accountant might say otherwise. I made auditing based on triggers like aforementioned. And now I need fill audit table with already presented data. But there is a problem. within trigger EXECUTE 'SELECT ($1).name::text' INTO newVal USING NEW; works fine but function (table decor has field name) CREATE OR REPLACE FUNCTION odb_InitLog() RETURNS void AS DECLARE obj record; BEGIN FOR obj IN (SELECT * FROM decor) LOOP EXECUTE 'SELECT ($1).name::text' INTO newVal USING obj; END LOOP; END; doesn't work - ERROR: could not identify column name in record data type Why? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature Request: DDL + RegExp - definitions
Merlin Moncure mmonc...@gmail.com writes: On Wed, Aug 24, 2011 at 11:30 AM, Richard Broersma The following email illustrates the behavior that is undesirable (to me). http://www.mail-archive.com/pgadmin-support@postgresql.org/msg11482.html this is IMO not a feature, but a bug. No, it's not a bug, it's just an unfortunate side-effect of the pushups we've had to go through to get to standard conforming strings. I seriously doubt that we would add a feature of the sort Richard suggests, because with standard_conforming_strings turned on (which is the default as of 9.1), there is no issue. Forcing E'' syntax would make the results of pg_getviewdef less standards-compliant, not more so, and that's not a direction we want to go in. If you find the warnings about backslashes annoying and unhelpful, you can turn off escape_string_warning --- they're only there to help identify code that is likely to have an issue when moved to an environment with standard_conforming_strings turned on. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plperlu function caused a segmentation fault
bricklen brick...@gmail.com writes: I was just testing a simple plperlu function to return the existence of a directory and as soon I pasted the function into psql and hit Enter, I got the following crash: FWIW, this function seems to work fine for me in 9.0.4 on a Fedora 14 x86_64 box ... and there haven't been any bug fixes in plperl since 9.0.2, except for this one http://git.postgresql.org/gitweb/?p=postgresql.gita=commitdiffh=cb252c2acd415d304e3254e99f82058d11a69e04 which seems unlikely to be related. I wonder if there is something wonky about your Perl installation. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plperlu function caused a segmentation fault
On Wed, Aug 24, 2011 at 11:28 AM, Tom Lane t...@sss.pgh.pa.us wrote: bricklen brick...@gmail.com writes: I was just testing a simple plperlu function to return the existence of a directory and as soon I pasted the function into psql and hit Enter, I got the following crash: FWIW, this function seems to work fine for me in 9.0.4 on a Fedora 14 x86_64 box ... and there haven't been any bug fixes in plperl since 9.0.2, except for this one http://git.postgresql.org/gitweb/?p=postgresql.gita=commitdiffh=cb252c2acd415d304e3254e99f82058d11a69e04 which seems unlikely to be related. I wonder if there is something wonky about your Perl installation. I tested originally on two other 9.0.4 databases and there were no issues at all. I'll have to assume that you are correct about the Perl setup on that server -- I'm quite leery of doing anything else with that server as it is a production db. I'll consider this a lesson learned, and not test any other Perl functions on that db. Cheers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What is postgresql status?
Hello all, Don't shoot me, I'm just the fella sweeping up after a departure. I'm supporting a web site with mapping features using Mapserver and PostgreSql 7.5 for windows. Recently, my mapping features went down. Error messages that display are the following... Warning: pg_query(): Query failed: ERROR: xlog flush request 0/34D85078 is not satisfied --- flushed only to 0/34CD4518 CONTEXT: writing block 0 of relation 394198/412173 in C:\ms4w\Apache\htdocs\davison\mapping\gis1\viewparcel_nolink.php on line 10 Warning: pg_fetch_row(): supplied argument is not a valid PostgreSQL result resource in C:\ms4w\Apache\htdocs\davison\mapping\gis1\viewparcel_nolink.php on line 12 It looks to me that the query failed based on a hard drive issue. Well, over the weekend we swapped out the hard drive for new. The site comes back fine but I'm still receiving the error. Another notice is when I shutdown and restart the database, I get this... C:\ms4w\apps\pgsql75wincd c:\ C:\cd ms4w/apps/pgsql75win/data/ C:\ms4w\apps\pgsql75win\datadel postmaster.pid Could Not Find C:\ms4w\apps\pgsql75win\data\postmaster.pid LOG: database system was shut down at 2011-08-24 17:30:14 Eastern Standard Time LOG: checkpoint record is at 0/34CD4078 LOG: redo record is at 0/34CD4078; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 1198832; next OID: 1772830 LOG: database system is ready I'm not sure if the database is up or not. I don't even see postgresql or mapserver as Windows services. I'm very confused. Thank you for any help with this continuing problem. John John W. McLeod Technology Integrator Spicer Group, Inc. 230 S. Washington Avenue Saginaw, MI 48607 Phone: (989) 921-5550 Fax: (989) 754-4440 mailto: jo...@spicergroup.commailto:jo...@spicergroup.com www.spicergroup.comhttp://www.spicergroup.com/ Stronger. Safer. Smarter. Spicer.
Re: [GENERAL] What is postgresql status?
On 24/08/2011 19:53, Mcleod, John wrote: Hello all, Don't shoot me, I'm just the fella sweeping up after a departure. I'm supporting a web site with mapping features using Mapserver and PostgreSql 7.5 for windows. There never was a PostgreSQL 7.5 release - it was changed to 8.0 at some point. Is this what you have? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plperlu function caused a segmentation fault
On Wed, Aug 24, 2011 at 12:32, bricklen brick...@gmail.com wrote: On Wed, Aug 24, 2011 at 11:28 AM, Tom Lane t...@sss.pgh.pa.us wrote: I wonder if there is something wonky about your Perl installation. I tested originally on two other 9.0.4 databases and there were no issues at all. I'll have to assume that you are correct about the Perl setup on that server -- I'm quite leery of doing anything else with that server as it is a production db. Hrm, do simple plperlu functions break it? I can't tell from your report if plperlu seems completely broken or if something in your quoted function seems to be the culprit. If CREATE OR REPLACE FUNCTION plperlu_noop() returns void as $$ $$ language plperlu; select plperlu_noop(); works fine, I would wager someone upgraded perl and forgot to recompile (or upgrade?) postgres along with it. That or you have more than one perl installed and its somehow picking the wrong libperl... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is postgresql status?
On 24/08/2011 19:53, Mcleod, John wrote: C:\ms4w\apps\pgsql75wincd c:\ C:\cd ms4w/apps/pgsql75win/data/ C:\ms4w\apps\pgsql75win\datadel postmaster.pid Could Not Find C:\ms4w\apps\pgsql75win\data\postmaster.pid LOG: database system was shut down at 2011-08-24 17:30:14 Eastern Standard Time LOG: checkpoint record is at 0/34CD4078 LOG: redo record is at 0/34CD4078; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 1198832; next OID: 1772830 LOG: database system is ready I'm not sure if the database is up or not. I don't even see postgresql or mapserver as Windows services. From the above I'd guess that Postgres isn't installed as a service, but is being run from a batch file. Postgres writes a small text file with its process ID in the data directory while it's running, and that's what the first line above is trying to delete - but PG removed it when it was shut down, so the DEL command isn't finding it. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is postgresql status?
On Wed, Aug 24, 2011 at 2:08 PM, Raymond O'Donnell r...@iol.ie wrote: On 24/08/2011 19:53, Mcleod, John wrote: Hello all, Don't shoot me, I'm just the fella sweeping up after a departure. I'm supporting a web site with mapping features using Mapserver and PostgreSql 7.5 for windows. There never was a PostgreSQL 7.5 release - it was changed to 8.0 at some point. Is this what you have? it is -- once every couple of months a windows user wanders in that's running 7.5 (alpha). There was so much pent up anticipation for the windows port that some people couldn't wait and adopted. OP -- take a database dump ASAP and migrate to either 8.2 or 9.0. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is postgresql status?
On 08/24/11 12:14 PM, Merlin Moncure wrote: OP -- take a database dump ASAP and migrate to either 8.2 or 9.0. one caveat, 8.4 (and 9.0) tightened up considerably the rules for implicit typecasting as there were a number of serious ambiguities in the sloppy way it was done before so code written for an earlier version might throw some SQL errors when migrated to 8.4 or 9.0+ these errors are generally quite easy to fix... cast to integer here, cast to text there, done. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is postgresql status?
On Wed, Aug 24, 2011 at 2:23 PM, John R Pierce pie...@hogranch.com wrote: On 08/24/11 12:14 PM, Merlin Moncure wrote: OP -- take a database dump ASAP and migrate to either 8.2 or 9.0. one caveat, 8.4 (and 9.0) tightened up considerably the rules for implicit typecasting as there were a number of serious ambiguities in the sloppy way it was done before so code written for an earlier version might throw some SQL errors when migrated to 8.4 or 9.0+ these errors are generally quite easy to fix... cast to integer here, cast to text there, done. it was in fact 8.3 that tightened them -- which why I suggested 8.2 :-). On Wed, Aug 24, 2011 at 2:31 PM, Mcleod, John jo...@spicergroup.com wrote: I talked with the guy that left me this mess and here's his reasoning behind NOT upgrading. 7.5 was the last version that used a text field for the geometries. All the new versions of postgis(postgresql) use a binary field for the geometries. I'm so new at this that I'm not sure if he's right or not. That sounds accurate. However, I'm not sure if that issue, which can be worked around without *too* much effort, justifies running a known broken out of support alpha release. I'm amazed you've made it this far without losing data. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PL/pgSQL trigger and sequence increment
Greetings. I noticed an interesting behavior when using a PL/pgSQL
trigger. I'm running PostgreSQL 8.3. The trigger function checks a
newly inserted or updated row for a type of uniqueness. Specifically,
each row in the table has a submitter id and an entry timestamp. No
two rows can have the same submitter id and entry timestamp month
(basically, this means that there can be one entry per submitter per
month). In other words, the trigger function is along the lines of:
BEGIN
IF NOT EXISTS (SELECT * FROM table_entry WHERE submitter_id =
new.submitter_id AND date_trunc('month',entry_timestamp) =
date_trunc('month',new.entry_timestamp)) THEN RETURN new;
ELSE RETURN NULL;
END IF;
END
Each row in the table also has a SERIAL identifier with a sequence
providing values. I'd like to provide information to the user
regarding why the INSERT or UPDATE failed, as the examples in the
documentation do via using a RAISE EXCEPTION instead of RETURN NULL
(see
http://www.postgresql.org/docs/8.3/interactive/plpgsql-trigger.html,
which appears to be unchanged in the documentation for 9.0). However,
if I do so, the sequence increments after the attempted INSERT or
UPDATE, which is not desired (and does not happen if RETURN NULL is
the result of the trigger function).
Any assistance is appreciated - thanks in advance!
---
Dominic Jones, Ph.D.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with 8.3.14 Windows binaries
Any ideas? Has anybody else had luck or problems with the new versions on Windows Server 2003? Thanks, -Pete From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Pete Wall Sent: Thursday, August 18, 2011 4:58 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Problem with 8.3.14 Windows binaries Hello, I am upgrading the version of PostgreSQL used in our product from 8.3.9 to 8.3.15 to close some of the vulnerabilities. After upgrading, I found that it wouldn't work on our Windows 2003 boxes, but it was fine on the 2008 ones. I downloaded every binary release from ..9 to ..15 (from here: http://www.postgresql.org/ftp/binary/) and found that the problem started with 8.3.14. What happens is when I try to launch any of the binaries, I get this message on the CLI: C:\tmp\postgresql-8.3.14-1-binaries-no-installer\pgsql\binpg_ctl.exe The system cannot execute the specified program. When I double-click it in Explorer, I get a dialog box with this message: This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem. I then opened it up in Dependency Walker and got this message: Error: The Side-by-Side configuration information for c:\tmp\postgresql-8.3.14-1-binaries-no-installer\pgsql\bin\PG_CTL.EXE contains errors. This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem (14001). Error: The Side-by-Side configuration information for c:\tmp\postgresql-8.3.14-1-binaries-no-installer\pgsql\bin\LIBPQ.DLL contains errors. This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem (14001). It was also not detecting the MSVCR80.DLL, while 8.3.13 found it automatically. Here's a screenshot of Dependency Walker comparing the new with the old: http://i.imgur.com/FxNkG.jpg Can someone help me figure out what's missing? I found Improve build support for Windows version here http://archives.postgresql.org/pgsql-announce/2011-02/msg0.php. Maybe that had something to do with it. Thanks, -Pete
Re: [GENERAL] What is postgresql status?
On Wed, Aug 24, 2011 at 1:37 PM, Merlin Moncure mmonc...@gmail.com wrote: be worked around without *too* much effort, justifies running a known broken Anything before 8.2 is considered broken, unfixable, not going to be fixed as far as windows is concerned. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plperlu function caused a segmentation fault
On Wed, Aug 24, 2011 at 12:11 PM, Alex Hunsaker bada...@gmail.com wrote: Hrm, do simple plperlu functions break it? I can't tell from your report if plperlu seems completely broken or if something in your quoted function seems to be the culprit. If CREATE OR REPLACE FUNCTION plperlu_noop() returns void as $$ $$ language plperlu; select plperlu_noop(); works fine, I would wager someone upgraded perl and forgot to recompile (or upgrade?) postgres along with it. That or you have more than one perl installed and its somehow picking the wrong libperl... IIRC, plperl(u) was installed via yum at the same time the db was upgraded to 9.0.x. It is possible there is more than one Perl version installed, or that the original Perl installation was botched. I'm not willing to test even the simple example above, because it's a production database. The orignal (failing) plperlu function didn't even get as far as the execution phase, that segfault happened immediately after it compiled. Cheers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OLD. || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)
On Wed, Aug 24, 2011 at 1:03 PM, ivan_14_32 ivan_14...@mail.ru wrote: 01.05.2011 12:58, Basil Bourque wrote: Hoorah! I was able to complete my single PL/pgSQL function to create history records tracking individual field value changes generically for all my tables. Some developers call this an audit trail, though an accountant might say otherwise. I made auditing based on triggers like aforementioned. And now I need fill audit table with already presented data. But there is a problem. within trigger EXECUTE 'SELECT ($1).name::text' INTO newVal USING NEW; works fine but function (table decor has field name) CREATE OR REPLACE FUNCTION odb_InitLog() RETURNS void AS DECLARE obj record; BEGIN FOR obj IN (SELECT * FROM decor) LOOP EXECUTE 'SELECT ($1).name::text' INTO newVal USING obj; END LOOP; END; doesn't work - ERROR: could not identify column name in record data type folks, this (dynamic field access of generic record in plpgsql) is the number one FAQ on this list. please check the archives before searching (not picking on you specifically, it just gets asked in some variant an awful lot). First point: hstore execute. if you _must_ use execute, you have to cast at some point. when you pass a record to something, it doesn't have the necessary context to know the field names. In your case, though, an explicit composite type is the way to go: DECLARE obj decor; BEGIN FOR obj IN SELECT * FROM decor LOOP newVal := obj.name; END LOOP; END; merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL trigger and sequence increment
jon...@xmission.com writes:
Greetings. I noticed an interesting behavior when using a PL/pgSQL
trigger. I'm running PostgreSQL 8.3. The trigger function checks a
newly inserted or updated row for a type of uniqueness. Specifically,
each row in the table has a submitter id and an entry timestamp. No
two rows can have the same submitter id and entry timestamp month
(basically, this means that there can be one entry per submitter per
month). In other words, the trigger function is along the lines of:
BEGIN
IF NOT EXISTS (SELECT * FROM table_entry WHERE submitter_id =
new.submitter_id AND date_trunc('month',entry_timestamp) =
date_trunc('month',new.entry_timestamp)) THEN RETURN new;
ELSE RETURN NULL;
END IF;
END
Seems like you would be a lot better off enforcing this with a unique
index on (submitter_id, date_trunc('month',entry_timestamp)). The above
not only doesn't provide any feedback, it's got serious race-condition
problems.
Each row in the table also has a SERIAL identifier with a sequence
providing values. I'd like to provide information to the user
regarding why the INSERT or UPDATE failed, as the examples in the
documentation do via using a RAISE EXCEPTION instead of RETURN NULL
(see
http://www.postgresql.org/docs/8.3/interactive/plpgsql-trigger.html,
which appears to be unchanged in the documentation for 9.0). However,
if I do so, the sequence increments after the attempted INSERT or
UPDATE, which is not desired (and does not happen if RETURN NULL is
the result of the trigger function).
Really? Frankly, I don't believe it. Any default value will get filled
in long before triggers run. In any case, you'd still have issues from
errors occurring later in the transaction. In general, you *can not*
expect to not have holes in the serial number assignment when using a
sequence object. You'll save yourself a lot of grief if you just accept
that fact, rather than imagining (falsely) that you've found a
workaround to avoid it.
If you really must have gap-free serial numbers, it's possible, but it's
slow, expensive, and doesn't rely on sequence objects. You can find the
details in the list archives, but basically each insert has to lock the
table against other inserts and then examine it to find the max current
id.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is postgresql status?
Scott Marlowe scott.marl...@gmail.com writes: On Wed, Aug 24, 2011 at 1:37 PM, Merlin Moncure mmonc...@gmail.com wrote: be worked around without *too* much effort, justifies running a known broken Anything before 8.2 is considered broken, unfixable, not going to be fixed as far as windows is concerned. Actually, anything before 8.2 is out of support on all platforms now. But running pre-alpha 8.0 on Windows is really seriously dangerous; the list of now-known bugs in that immature port will curl your toes. I second the advice to the OP to get off that version *now*. 8.2.some-recent-minor-release is the minimum version you should be running if you're on Windows. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plperlu function caused a segmentation fault
bricklen brick...@gmail.com writes: On Wed, Aug 24, 2011 at 12:11 PM, Alex Hunsaker bada...@gmail.com wrote: Hrm, do simple plperlu functions break it? IIRC, plperl(u) was installed via yum at the same time the db was upgraded to 9.0.x. It is possible there is more than one Perl version installed, or that the original Perl installation was botched. I'm not willing to test even the simple example above, because it's a production database. The orignal (failing) plperlu function didn't even get as far as the execution phase, that segfault happened immediately after it compiled. I agree you probably don't want to poke at this in your production instance, but you could create a playpen instance (separate data directory, nondefault port number) using the same executables and then do your testing there. If you didn't want to get plperl going on that machine, why'd you try it in the first place? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plperlu function caused a segmentation fault
On Wed, Aug 24, 2011 at 1:16 PM, Tom Lane t...@sss.pgh.pa.us wrote: I agree you probably don't want to poke at this in your production instance, but you could create a playpen instance (separate data directory, nondefault port number) using the same executables and then do your testing there. If you didn't want to get plperl going on that machine, why'd you try it in the first place? Good idea about using a separate data dir etc, I'll try that out. As far as not wanting plperl in the first place, I didn't mean to imply that. I've used plperl(u) functions before (but not since 8.4), so it was never an issue on this particular production machine before. I've had no problems in the other dev and test databases where I've found uses for plperl functions -- none are currently lower than 9.0.4 however. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql server crash on windows 7 when using plpython
On 08/24/2011 10:06 AM, c k wrote: No, I have added the python directory in the PATH. Another thing is I created another language plpython2u and succeeded. Still I will try to figure out the problem. How did you create plpythonu2? Define succeed; the language was created or the language was created and functions written with it ran successfully? Regards, Chaitanya Kulkarni -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how is max_fsm_pages configured in 8.4
I see that max_fsm_pages isn't there anymore in postgresql.conf from 8.4. Why is it? And can it be configured, or is it something we should not worry about? -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how is max_fsm_pages configured in 8.4
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Martín Marqués Sent: Wednesday, August 24, 2011 2:48 PM To: pgsql-general Subject: [GENERAL] how is max_fsm_pages configured in 8.4 I see that max_fsm_pages isn't there anymore in postgresql.conf from 8.4. Why is it? From: http://www.postgresql.org/docs/8.4/static/release-8-4.html E.9.3.5.3. VACUUM Track free space in separate per-relation fork files (Heikki) Free space discovered by VACUUM is now recorded in *_fsm files, rather than in a fixed-sized shared memory area. The max_fsm_pages and max_fsm_relations settings have been removed, greatly simplifying administration of free space management. And can it be configured, no or is it something we should not worry about? You shouldn't need to worry about it anymore. -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Indexes on inheriting tables
Hi, I'm using Pg 9.0 and inheritance to do table partitioning. A simple example would be: CREATE TABLE foo ( id INTEGER PRIMARY KEY, thing VARCHAR(32) ); CREATE INDEX foo_thing_idx ON foo(thing); CREATE TABLE foo_1 () INHERITS (foo); I read that foreign key constraints wouldn't be inherited. However I am also finding that indexes aren't inherited either. eg. EXPLAIN SELECT id FROM foo WHERE thing='something'; will indicate that an indexed scan will be done over foo, followed by a sequential scan over foo_1. Do I need to make sure I re-create every index on every child table I create? That would be.. annoying, at best. Is there a way to enable inheritance of indexes too? Cheers, Toby -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes on inheriting tables
On Thu, Aug 25, 2011 at 11:17 AM, Toby Corkindale toby.corkind...@strategicdata.com.au wrote: Do I need to make sure I re-create every index on every child table I create? That would be.. annoying, at best. Is there a way to enable inheritance of indexes too? You do not need an index on the master table so create indexes on child tables only. This way you are avoiding big index scans and only looking at the index on the data you are interested in (child table). cheers, Shoaib
Re: [GENERAL] Indexes on inheriting tables
On 25/08/11 11:34, Shoaib Mir wrote: On Thu, Aug 25, 2011 at 11:17 AM, Toby Corkindale toby.corkind...@strategicdata.com.au mailto:toby.corkind...@strategicdata.com.au wrote: Do I need to make sure I re-create every index on every child table I create? That would be.. annoying, at best. Is there a way to enable inheritance of indexes too? You do not need an index on the master table so create indexes on child tables only. This way you are avoiding big index scans and only looking at the index on the data you are interested in (child table). Ah, but I'd rather not maintain all those extra indexes! Say I want to add a new index - now I'd need to go through and add it to a hundred tables! I guess I can script it.. but it doesn't seem good. It seems messy to inherit the columns but not the indexes or checks upon them :( Toby -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes on inheriting tables
On Thu, Aug 25, 2011 at 11:57 AM, Toby Corkindale toby.corkind...@strategicdata.com.au wrote: It seems messy to inherit the columns but not the indexes or checks upon them :( Yes it can be a bit annoying at timse but you can try to automate the whole process as well. Like I found this blog entry -- http://blog.inovia.fr/auto-partitioning-on-postgresql-part-1/ cheers, Shoaib
Re: [GENERAL] Collapsing multiple subqueries into one
On 24/08/2011, at 4:44 PM, Chris Hanks wrote: Thanks Royce. I put together another query using a WITH statement that's also working: WITH v AS ( SELECT item_id, type, direction, array_agg(user_id) as user_ids FROM votes WHERE root_id = 5305 GROUP BY type, direction, item_id ORDER BY type, direction, item_id ) SELECT *, (SELECT user_ids from v where item_id = i.id AND type = 0 AND direction = 1) as upvoters, (SELECT user_ids from v where item_id = i.id AND type = 0 AND direction = -1) as downvoters, (SELECT user_ids from v where item_id = i.id AND type = 1) as favoriters FROM items i WHERE root_id = 5305 ORDER BY id It feels more sensible to me, but it's slightly slower than my initial attempt (15 ms vs. 13 ms, when running as a prepared statement to avoid any query parsing overhead, and averaging the time over several thousand queries). I'm not sure why...? I'm not sure, Chris - perhaps others on the mailing list can answer this? On Tue, Aug 23, 2011 at 8:14 PM, Royce Ausburn royce...@inomial.com wrote: This might help you: http://www.postgresql.org/docs/8.4/static/queries-with.html On 24/08/2011, at 9:54 AM, Chris Hanks wrote: I have two tables: CREATE TABLE items ( root_id integer NOT NULL, id serial NOT NULL, -- Other fields... CONSTRAINT items_pkey PRIMARY KEY (root_id, id) ) CREATE TABLE votes ( root_id integer NOT NULL, item_id integer NOT NULL, user_id integer NOT NULL, type smallint NOT NULL, direction smallint, CONSTRAINT votes_pkey PRIMARY KEY (root_id, item_id, user_id, type), CONSTRAINT votes_root_id_fkey FOREIGN KEY (root_id, item_id) REFERENCES items (root_id, id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, -- Other constraints... ) I'm trying to, in a single query, pull out all items of a particular root_id along with a few arrays of user_ids of the users who voted in particular ways. The following query does what I need: SELECT *, ARRAY(SELECT user_id from votes where root_id = i.root_id AND item_id = i.id AND type = 0 AND direction = 1) as upvoters, ARRAY(SELECT user_id from votes where root_id = i.root_id AND item_id = i.id AND type = 0 AND direction = -1) as downvoters, ARRAY(SELECT user_id from votes where root_id = i.root_id AND item_id = i.id AND type = 1) as favoriters FROM items i WHERE root_id = 1 ORDER BY id The problem is that I'm using three subqueries to get the information I need when it seems like I should be able to do the same in one. I thought that Postgres (I'm using 8.4) might be smart enough to collapse them all into a single query for me, but looking at the explain output in pgAdmin it looks like that's not happening - it's running multiple primary key lookups on the votes table instead. I feel like I could rework this query to be more efficient, but I'm not sure how. Any pointers? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes on inheriting tables
On 25/08/11 12:10, Shoaib Mir wrote: On Thu, Aug 25, 2011 at 11:57 AM, Toby Corkindale toby.corkind...@strategicdata.com.au mailto:toby.corkind...@strategicdata.com.au wrote: It seems messy to inherit the columns but not the indexes or checks upon them :( Yes it can be a bit annoying at timse but you can try to automate the whole process as well. Like I found this blog entry -- http://blog.inovia.fr/auto-partitioning-on-postgresql-part-1/ As far as I could tell, that isn't automating anything to do with indexes or checks on the child tables? Although using a trigger to automate the creation of the child table itself is still nifty. I hadn't thought of that. thanks, Toby -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes on inheriting tables
Hi, On 25 August 2011 11:17, Toby Corkindale toby.corkind...@strategicdata.com.au wrote: Do I need to make sure I re-create every index on every child table I create? That would be.. annoying, at best. Yes, it is little bit annoying but I like it. You don't need any index on parent table but you have to create them manually. I wrote simple python script which creates partitions and required indexes in advance (tables are partitioned by date). I like the flexibility because you can have different indexex on different partitions. For example, I discovered that adding index will improve several queries. In the production I can't afford exclusive lock (build index concurrently takes ages) so I updated and re-run the script which re-created future partitions. -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Links to Replication
Dear all, I am using PostgresPlus-8.4SS version of Postgres on Linux Windows Systems. Now I need to enable replication of two servers. OS may be same or different. Please let me know any useful links to do that. Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Links to Replication
On Thu, Aug 25, 2011 at 2:03 PM, Adarsh Sharma adarsh.sha...@orkash.comwrote: Dear all, I am using PostgresPlus-8.4SS version of Postgres on Linux Windows Systems. Now I need to enable replication of two servers. OS may be same or different. Please let me know any useful links to do that. I am not really sure what PostgresPlus-8.4SS is? but if you were using PostgreSQL 8.4 then Slony (http://slony.info/) can be a good option, otherwise upgrade to 9.0 and go with streaming replication which out-of-box. cheers, Shoaib
