Re: [GENERAL] Rules, Windows and ORDER BY
2012/8/23 Tom Lane t...@sss.pgh.pa.us: Jason Dusek jason.du...@gmail.com writes: I have a simple table of keys and values which periodically receives updated values. It's desirable to keep older values but, most of the time, we query only for the latest value of a particular key. CREATE TABLE kv ( k bytea NOT NULL, at timestamptz NOT NULL, realm bytea NOT NULL, v bytea NOT NULL ); CREATE INDEX ON kv USING hash(k); CREATE INDEX ON kv (t); CREATE INDEX ON kv USING hash(realm); SELECT * FROM kv WHERE k = $1 AND realm = $2 ORDER BY at DESC LIMIT 1; If you want to make that fast, an index on (k,realm,at) would help. Those indexes that you did create are next to useless for this, and furthermore hash indexes are quite unsafe for production. Thanks for pointing out the unsafety of hash indexes. I think I got in the habit of using them for a project with large, temporary data sets. Why are the individual indices not useful? The tests that the query does -- equality on key and realm and ordering on at -- are each supported by indices. Does it have to do with the cost of loading the three indices? -- Jason Dusek pgp // solidsnack // C1EBC57DC55144F35460C8DF1FD4C6C1FED18A2B -- 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] Rules, Windows and ORDER BY
On Fri, Aug 24, 2012 at 09:32:32AM +, Jason Dusek wrote: 2012/8/23 Tom Lane t...@sss.pgh.pa.us: Jason Dusek jason.du...@gmail.com writes: CREATE TABLE kv ( k bytea NOT NULL, at timestamptz NOT NULL, realm bytea NOT NULL, v bytea NOT NULL ); CREATE INDEX ON kv USING hash(k); CREATE INDEX ON kv (t); CREATE INDEX ON kv USING hash(realm); SELECT * FROM kv WHERE k = $1 AND realm = $2 ORDER BY at DESC LIMIT 1; If you want to make that fast, an index on (k,realm,at) would help. Those indexes that you did create are next to useless for this, and furthermore hash indexes are quite unsafe for production. Why are the individual indices not useful? The tests that the query does -- equality on key and realm and ordering on at -- are each supported by indices. Does it have to do with the cost of loading the three indices? I'm not entirely sure, but I'll take a stab at it. I think it has to do with the fact that you want order. Combining multiple indexes so you use them at the same time works as an BitmapAnd. That is, it uses each index to determine blocks that are interesting and then find the blocks that are listed by all tindexes, and then it loads the blocks and chcks them. The problem here is that you want ORDER BY at, which makes the above scheme fall apart, because order is not preversed. So it falls back on either scanning the 'at' index and probing checking the rows to see if they match, or using all indexes, and then sorting the result. In theory you could BitmapAnd the 'k' and 'realm' indexes and then scan the 'at' index only checking rows that the bitmap shows are interesting. But I'm not sure if postgres can do that. Anyway, the suggested three column index will match your query in a single lookup and hence be much faster than any of the above suggestions, so if this is a really important query then it may be worth it here. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] FETCH in subqueries or CTEs
On 08/24/2012 12:34 PM, Pavel Stehule wrote: you can't mix planned and unplanned statements together - think about stored plans every time Thanks Pavel and Jeff. I can't say I fully understand the arguments, but I'll take it that accepting cursors in CTEs or subqueries wouldn't make sense. I guess the main issue really is that you'd have to materialize them anyway to avoid issues with multiple scans, so there's little point having a cursor. I didn't find a reasonable way to simply fetch a cursor into a (possibly temporary) table, like: INSERT INTO sometable FETCH ALL FROM somecursor; ... which could be handy with PL/PgSQL functions that return multiple refcursors. It only seems to be possible via a PL/PgSQL wrapper that loops over the cursor and returns a rowset. -- Craig Ringer -- 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] Windows SIngle Sign On - LINUX Server
LDAP will be your best choice for SSO, Ubuntu Linux can authenticate against AD. Also this is OS stuff not PostgreSQL, if you server is in production and you can not handle this migration, it is advisable that you subscribe for support on Ubuntu from canonical Thanks, Sunday Olutayo - Original Message - From: Jeremy Palmer jpal...@linz.govt.nz To: pgsql-general@postgresql.org Sent: Thursday, August 23, 2012 8:12:55 PM Subject: [GENERAL] Windows SIngle Sign On - LINUX Server Hi All, We are currently running PostgreSQL 8.4 on Windows server 2003 and are planning to move the instance to Ubuntu 10.4 - yay!. At the same time we will also upgrade to 9.1. One nice features that we leverage from the windows configuration is the ability for windows clients to use AD SSO i.e SSPI. This was really easy to set-up and configure. If we move to Linux I was wondering if anyone could provide a howto reference or some tips on how to set-up auth configuration to provide SSO to windows clients? I've read the GSSAPI/Kerberos authentication section of the docs, but it's still unclear to me the exact process to follow. In particular what things would I need to configure on the Ubuntu server and which tasks will need to be done on the Windows domain controller (which is managed by a third party service provider who knows little about PostgreSQL or Ubuntu) We are using a WIndows server 2008 for the domain control. However I know little about it's setup or configuration, I only know it's our windows domain realm. Regards, Jeremy This message contains information, which is confidential and may be subject to legal privilege. If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify us immediately (Phone 0800 665 463 or i...@linz.govt.nz) and destroy the original message. LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ. Thank You. -- 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] I: Installation faillure version 8.4.12
Dear All We try to install, several times, postgres version 8.4.12, each installation was failed during the post-install step displaying this error: (install-postgresql.log) Executing cscript //NoLogo F:\postgressql/installer/server/initcluster.vbs postgres postgres F:\postgressql F:\postgressql\data 5432 DEFAULT Script exit code: 1 Script output: Errore CScript: Impossibile trovare l'interprete di script VBScript per lo script F:\postgressql\installer\server\initcluster.vbs. Script stderr: Program ended with an error exit code Error running cscript //NoLogo F:\postgressql/installer/server/initcluster.vbs postgres postgres F:\postgressql F:\postgressql\data 5432 DEFAULT : Program ended with an error exit code Problem running post-install step. Installation may not complete correctly The database cluster initialisation failed. We tried with command: regsvr32 %systemroot%\system32\vbscript.dll but the problem was not solved Could you suggest us , some solutions ? Best regards Luca Segato Dr. Luca Segato R.E.A.C.H. Manager Tel. +39 02 99442252 Fax. +30 02 99442265 lseg...@prholding.it http://www.prholding.it PR HOLDING S.P.A. HEADQUARTERS Via Milano, 186 20024 Garbagnate Milanese (MI) - ITALY DICHIARAZIONE DI RISERVATEZZA Le informazioni contenute nella comunicazione che precede e negli eventuali allegati possono essere riservate e sono, comunque, destinate alla persona o all'ente indicati come destinatari. La diffusione, copiatura e/o distribuzione del documento trasmesso da parte di qualsiasi soggetto diverso dal destinatario e proibita e potra essere legalmente persegutia.Se avete ricevuto questo messaggio per errore, Vi preghiamo di contattarci immediatamente e di distruggere la comunicazione e tutti gli eventuali allegati CONFIDENTIAL STATEMENT This e-mail and any attachements may contain confidential and/or proprietary information that are for the exclusive use of the intended recipient only.The disclosure, copying, distribution or any other use of this e-mail by any other than the intended recipient is strictly prohibited and may result in legal action. If you have recived this e-mail in error, please immediately inform us then delete the e-mail and all enclosed attachments
Re: [GENERAL] I: Installation faillure version 8.4.12
On Fri, Aug 24, 2012 at 4:47 PM, Segato Luca lseg...@prholding.it wrote: ** ** Dear All We try to install, several times, postgres version 8.4.12, each installation was failed during the “post-install step ” displaying this error: (install-postgresql.log) ** ** *Executing cscript //NoLogo F:\postgressql/installer/server/initcluster.vbs postgres postgres F:\postgressql F:\postgressql\data 5432 DEFAULT* *Script exit code: 1* * * *Script output:* * **Errore CScript: Impossibile trovare l'interprete di script VBScript per lo script F:\postgressql\installer\server\initcluster.vbs.* * * *Script stderr:* * Program ended with an error exit code* * * *Error running cscript //NoLogo F:\postgressql/installer/server/initcluster.vbs postgres postgres F:\postgressql F:\postgressql\data 5432 DEFAULT : Program ended with an error exit code* *Problem running post-install step. Installation may not complete correctly* * The database cluster initialisation failed.* * * We tried with command*: **regsvr32 %systemroot%\system32\vbscript.dll** **but the problem was not solved*** * * *Could you suggest us , some solutions ?* Can you attach the installation logs? And - also mention the operating system. Can -- Thanks Regards, Ashesh Vashi EnterpriseDB INDIA: Enterprise PostgreSQL Companyhttp://www.enterprisedb.com/ *http://www.linkedin.com/in/asheshvashi* ** * * *Best regards* * * *Luca Segato*** ** ** *Dr. Luca Segato* *R.E.A.C.H. Manager* Tel. +39 02 99442252 Fax. +30 02 99442265 *lseg...@prholding.it* *http://www.prholding.it* *PR HOLDING S.P.A.* *HEADQUARTERS* *Via Milano, 186* *20024 Garbagnate Milanese (MI) - ITALY* ** ** *DICHIARAZIONE DI RISERVATEZZA* Le informazioni contenute nella comunicazione che precede e negli eventuali allegati possono essere riservate e sono, comunque, destinate alla persona o all'ente indicati come destinatari. La diffusione, copiatura e/o distribuzione del documento trasmesso da parte di qualsiasi soggetto diverso dal destinatario è proibita e potrà essere legalmente perseguita. Se avete ricevuto questo messaggio per errore, Vi preghiamo di contattarci immediatamente e di distruggere la comunicazione e tutti gli eventuali allegati *CONFIDENTIAL STATEMENT* This e-mail and any attachments may contain confidential and/or proprietary information that are for the exclusive use of the intended recipient only.The disclosure, copying, distribution or any other use of this e-mail by any other than the intended recipient is strictly prohibited and may result in legal action. If you have received this e-mail in error, please immediately inform us then delete the e-mail and all enclosed attachments
Re: [GENERAL] Interval 1 month is equals to interval 30 days - WHY?
BTW there are a much more short version of this: CREATE OR REPLACE FUNCTION int_equal(interval, interval) RETURNS boolean IMMUTABLE STRICT LANGUAGE sql AS 'SELECT $1::text = $2::text'; On Wed, Aug 8, 2012 at 4:51 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote: Then maybe you should use something like this for equality: CREATE OR REPLACE FUNCTION int_equal(interval, interval) RETURNS boolean IMMUTABLE STRICT LANGUAGE sql AS 'SELECT 12 * EXTRACT (YEAR FROM $1) + EXTRACT (MONTH FROM $1) = 12 * EXTRACT (YEAR FROM $2) + EXTRACT (MONTH FROM $2) AND EXTRACT (DAY FROM $1) = EXTRACT (DAY FROM $2) AND 36 * EXTRACT (HOUR FROM $1) + 6000 * EXTRACT (MINUTE FROM $1) + EXTRACT (MICROSECONDS FROM $1) = 36 * EXTRACT (HOUR FROM $2) + 6000 * EXTRACT (MINUTE FROM $2) + EXTRACT (MICROSECONDS FROM $2)';
Re: [GENERAL] Windows SIngle Sign On - LINUX Server
On 08/24/2012 06:10 PM, SUNDAY A. OLUTAYO wrote: LDAP will be your best choice for SSO, Ubuntu Linux can authenticate against AD. I'm not at all convinced by that. Active Directory functions as a Kerberos KDC. Kerberos provides secure authentication and (unlike LDAP) single sign-on. http://technet.microsoft.com/en-us/library/bb742516.aspx Use Kerberos via GSSAPI. Here's a good starting point by Marcus: http://www.hagander.net/talks/Deploying%20PostgreSQL%20in%20a%20Windows%20Enterprise.pdf -- Craig Ringer -- 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] Windows SIngle Sign On - LINUX Server
In real world deployment, LDAP and Kerbero are often combined for authentication and authorization. The link below is a well documented howto: https://help.ubuntu.com/community/SingleSignOn Thanks, Sunday Olutayo - Original Message - From: Craig Ringer ring...@ringerc.id.au To: SUNDAY A. OLUTAYO olut...@sadeeb.com Cc: Jeremy Palmer jpal...@linz.govt.nz, pgsql-general@postgresql.org Sent: Friday, August 24, 2012 12:48:01 PM Subject: Re: [GENERAL] Windows SIngle Sign On - LINUX Server On 08/24/2012 06:10 PM, SUNDAY A. OLUTAYO wrote: LDAP will be your best choice for SSO, Ubuntu Linux can authenticate against AD. I'm not at all convinced by that. Active Directory functions as a Kerberos KDC. Kerberos provides secure authentication and (unlike LDAP) single sign-on. http://technet.microsoft.com/en-us/library/bb742516.aspx Use Kerberos via GSSAPI. Here's a good starting point by Marcus: http://www.hagander.net/talks/Deploying%20PostgreSQL%20in%20a%20Windows%20Enterprise.pdf -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] create table like . . . constraint names
Is there any way for me to control the name of the (unique or primary) constraints that get created when doing a create table like parent-table statement ? I use this statement to create the new table in a different schema than the one in which the parent-table lives, and I would like the constraint names to be the same as those of the parent-table. thanks, -dvs-
Re: [GENERAL] FETCH in subqueries or CTEs
2012/8/24 Craig Ringer ring...@ringerc.id.au: On 08/24/2012 12:34 PM, Pavel Stehule wrote: you can't mix planned and unplanned statements together - think about stored plans every time Thanks Pavel and Jeff. I can't say I fully understand the arguments, but I'll take it that accepting cursors in CTEs or subqueries wouldn't make sense. I guess the main issue really is that you'd have to materialize them anyway to avoid issues with multiple scans, so there's little point having a cursor. I didn't find a reasonable way to simply fetch a cursor into a (possibly temporary) table, like: INSERT INTO sometable FETCH ALL FROM somecursor; it should be implemented as function - like materialize_cursor(cursor, table) I would to see full support of stored procedures (with multirecordsets) rather. Regards Pavel ... which could be handy with PL/PgSQL functions that return multiple refcursors. It only seems to be possible via a PL/PgSQL wrapper that loops over the cursor and returns a rowset. -- Craig Ringer -- 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] FETCH in subqueries or CTEs
Craig Ringer ring...@ringerc.id.au writes: I didn't find a reasonable way to simply fetch a cursor into a (possibly temporary) table, like: INSERT INTO sometable FETCH ALL FROM somecursor; Why would you bother with a cursor, and not just INSERT ... SELECT using the original query? Putting a cursor in between will just make matters more complicated and slower. (For one thing, the plan created for a cursor is optimized for incremental fetching not read-it-all-at-once.) 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] Rules, Windows and ORDER BY
Martijn van Oosterhout klep...@svana.org writes: On Fri, Aug 24, 2012 at 09:32:32AM +, Jason Dusek wrote: Why are the individual indices not useful? The tests that the query does -- equality on key and realm and ordering on at -- are each supported by indices. Does it have to do with the cost of loading the three indices? I'm not entirely sure, but I'll take a stab at it. I think it has to do with the fact that you want order. Combining multiple indexes so you use them at the same time works as an BitmapAnd. That is, it uses each index to determine blocks that are interesting and then find the blocks that are listed by all tindexes, and then it loads the blocks and chcks them. Yeah. While you *can* in principle solve the problem with the individual indexes, it's much less efficient than a single index. In particular, BitmapAnd plans are far from being a magic bullet for combining two individually-not-very-selective conditions. (That realm constraint is surely not very selective; dunno about the key one.) That implies reading a large number of entries from each index, forming a rather large bitmap for each one, and then ANDing those bitmaps to get a smaller one. And even after all that work, you're still not done, because you have no idea which bit in the bitmap represents the row with largest at value. In theory you could BitmapAnd the 'k' and 'realm' indexes and then scan the 'at' index only checking rows that the bitmap shows are interesting. But I'm not sure if postgres can do that. No, it can't, and that likely wouldn't be a very effective plan anyway; you could end up scanning a very large fraction of the at index, since you'd have to start at the end (the latest entry anywhere in the table). Even if you didn't make many trips to the heap, that's not cheap. In constrast, given a three-column btree index organized with the equality-constrained columns first, the btree code can descend the index tree straight to the entry you want. We've expended a lot of sweat on optimizing that case, and it will absolutely blow the doors off anything involving a bitmap scan. Of course the downside is that the three-column index might be relatively useless for queries of forms other than this one. So it's a tradeoff between flexibility and performance. But since the OP is asking, I'm assuming he cares a lot about performance of queries of this exact form. 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] Check PostgreSQL status using MS-DOS bat file?
Hi Dinesh, Managed to get this up and running...thanks!! Regards Hugh From: dinesh kumar [mailto:dineshkuma...@gmail.com] Sent: 17 August 2012 13:15 To: Loughrey, Hugh Cc: Postgres General Subject: Re: [GENERAL] Check PostgreSQL status using MS-DOS bat file? Hi , Dave's instructions are helpful for finding the status of the server.. However, I do have the below the script which is nothing but PgPing in windows ... I hope it helps you in the implementation .. @ECHO OFF set PSQL=C:\Program Files\PostgreSQL\9.1\bin set DBNAME=template1 set USER=postgres set PORT=5432 set RES=Not Pinging %PSQL%\psql -Atq -c SELECT 'ping' -p %PORT% -U %USER% %DBNAME% _Res.txt set /p RES=_Res.txt echo %RES% IF %RES% EQU ping (echo No need to raise any exception ) else (echo PostgreSQL seems not pinging.. Need to raise an exception) Best Regards, Dinesh manojadinesh.blogspot.comhttp://manojadinesh.blogspot.com On Fri, Aug 17, 2012 at 4:32 PM, Dave Page dp...@pgadmin.orgmailto:dp...@pgadmin.org wrote: [Please keep the mailing list CC'd] On Fri, Aug 17, 2012 at 11:52 AM, Loughrey, Hugh hugh.lough...@hoopleltd.co.ukmailto:hugh.lough...@hoopleltd.co.uk wrote: Hi Dave, Thanks for the message below. The script you forwarded looks to be for an instance in which the DB is running of a windows box, apologies I should have mentioned, we currently run PostgreSQL on a Linux box. Does this mean calling pg_ctl is not an option? Or do we need to install additional drivers? pg_ctl only checks the status of an instance running on the local machine. To check on a remote linux box from windows using pg_ctl, you'd have to run pg_ctl on the linux box, probably over SSH (look for Putty for an SSH client for Windows). An easier option might be to run a psql command on the remote database. Just run something like SELECT 1 and check you actually get a 1 back, and not a connection error. We'd be using FME to push data from other databases into PostgreSQL, however before running our FME scripts we'd want to check the DB is up and running. If the DB is running, then run the FME scripts, if not...don't run the FME scripts. All of this needs to be controlled via a windows server 2008 box. I appreciate your help. Regards Hugh -Original Message- From: Dave Page [mailto:dp...@pgadmin.orgmailto:dp...@pgadmin.org] Sent: 15 August 2012 16:30 To: Bruce Momjian Cc: Loughrey, Hugh; pgsql-general@postgresql.orgmailto:pgsql-general@postgresql.org Subject: Re: [GENERAL] Check PostgreSQL status using MS-DOS bat file? On Wed, Aug 15, 2012 at 4:04 PM, Bruce Momjian br...@momjian.usmailto:br...@momjian.us wrote: On Wed, Aug 15, 2012 at 09:52:17AM +, Loughrey, Hugh wrote: Hi All, I want to write a MS-DOS command to check that the PostgreSQL database is up and running and able to accept data being pushed to it. From a bit of reading I've identified the pg_ctl status command, but can this be incorporated into a *.bat file and can the resulting status be recorded in a *.txt file? If so does anyone have a command which would enable this? As I remember it is pretty tricky to call pg_ctl from a Windows batch file. I know the Windows installers do it somehow --- you might want to downlaod it and see if you can find the shell script they use. Dave Page might know more --- CC'ing him. You shouldn't try to start/stop the server with pg_ctl if it's configured to run as a service (use net start xxx, net stop xxx for that), but you can check the status: C:\C:\Program Files\PostgreSQL\9.2\bin\pg_ctl.exe -D C:\Program Files\PostgreSQL\9.2\data status pg_ctl: server is running (PID: 1040) C:/Program Files/PostgreSQL/9.2/bin/postgres.exe -D C:/Program Files/PostgreSQL/9.2/data C:\net stop postgresql-x64-9.2 The postgresql-x64-9.2 service is stopping. The postgresql-x64-9.2 service was stopped successfully. C:\C:\Program Files\PostgreSQL\9.2\bin\pg_ctl.exe -D C:\Program Files\PostgreSQL\9.2\data status pg_ctl: no server running -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company Any opinion expressed in this e-mail or any attached files are those of the individual and not necessarily those of Hoople Ltd. You should be aware that Hoople Ltd. monitors its email service. This e-mail and any attached files are confidential and intended solely for the use of the addressee. This communication may contain material protected by law from being passed on. If you are not the intended recipient and have received this e-mail in error, you are advised that any use, dissemination, forwarding, printing or copying of this e-mail is strictly prohibited. If you have received this e-mail in error please contact the sender immediately and destroy all copies of it. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The
Re: [GENERAL] At what point does a big table start becoming too big?
On 23/08/12 11:06, Nick wrote: I have a table with 40 million rows and haven't had any performance issues yet. Are there any rules of thumb as to when a table starts getting too big? For example, maybe if the index size is 6x the amount of ram, if the table is 10% of total disk space, etc? I think it would be good to specify the context. For example: The timeliness of a database required to support an ship based anti-missile system would require far more stringent timing considerations than a database used to retrieve scientific images based on complicated criteria. The size of records, how often updated/deleted, types of queries, ... would also be useful. Unfortunately it might simply be a case of It depends...! Cheers, Gavin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] run function on server restart
Sometimes the server process crashes and restarts, usually when I run some large calculations that eat up all available memory. Is there any way to detect this and run a cleanup routine when it happens? Running 8.4 on Ubuntu. -- John
[GENERAL] Overlapping time ranges constraints in 8.4
I have a temporal data question that may be much easier to handle in version 9.x but I am stuck on version 8.4. One table has a time range that is implemented as start_time and end_time columns of type TIMESTAMP with Timezone. A second table has information that is needed to determine if there is a schedule conflict in the items in the first table. I am considering using row level INSERT and UPDATE triggers to prevent overlapping time ranges. TABLE campus ( id SERIAL, foo BOOLEAN NOT NULL, ... PRIMARY KEY (id) ) ; TABLE B ( id SERIAL, campus_id INTEGER NOT NULL, start_time timestamp NOT NULL, stop_time timestamp NOT NULL, ... PRIMARY KEY (id), FOREIGN KEY (campus_id) REFERENCES campus(id) ON DELETE CASCADE; ); Records in table B are not considered overlapping if their campus has its foo column set to FALSE. In my triggers (PL/pgSQL) I am using a expression like this SELECT B.* INTO v_overlapping from INNER JOIN campus ON (campus.id=B.campus_id) where campus.colA = 't' AND (campus.start_time, campus.stop_time) OVERLAPS (NEW.start_time, NEW.stop_TIME); I am worried that the transaction serialization will not do the predicate locking that is needed for concurrent inserts/updates. Can I use add a FOR UPDATE clause to my SELECT INTO expression in PL/pgSQL ? Pete Rothermel
Re: [GENERAL] Overlapping time ranges constraints in 8.4
EXT-Rothermel, Peter M peter.m.rother...@boeing.com wrote: I have a temporal data question that may be much easier to handle in version 9.x but I am stuck on version 8.4. That is unfortunate. Getting this to work correctly in 8.4 will probably be a lot more work than upgrading to 9.1 and getting it to work there. One table has a time range that is implemented as start_time and end_time columns of type TIMESTAMP with Timezone. A second table has information that is needed to determine if there is a schedule conflict in the items in the first table. I am considering using row level INSERT and UPDATE triggers to prevent overlapping time ranges. TABLE campus ( id SERIAL, foo BOOLEAN NOT NULL, ... PRIMARY KEY (id) ) ; TABLE B ( id SERIAL, campus_id INTEGER NOT NULL, start_time timestamp NOT NULL, stop_time timestamp NOT NULL, ... PRIMARY KEY (id), FOREIGN KEY (campus_id) REFERENCES campus(id) ON DELETE CASCADE; ); Records in table B are not considered overlapping if their campus has its foo column set to FALSE. In my triggers (PL/pgSQL) I am using a expression like this SELECT B.* INTO v_overlapping from INNER JOIN campus ON (campus.id=B.campus_id) where campus.colA = 't' AND (campus.start_time, campus.stop_time) OVERLAPS (NEW.start_time, NEW.stop_TIME); I am worried that the transaction serialization will not do the predicate locking that is needed for concurrent inserts/updates. To get that sort of predicate locking in PostgreSQL, you must be using version 9.1 or later and the transactions must be using the serializable transaction isolation level. But for something like this, you might be better off using the exclusion constraint feature of 9.0 and later. (The only reason I say might instead of would is that I'm not sure that feature can handle the complication of the boolean in a separate table.) Can I use add a FOR UPDATE clause to my SELECT INTO expression in PL/pgSQL ? That won't help -- it just locks the actual rows read; it doesn't protect against insertion of conflicting rows. You could use explicit locking to actually serialize the transactions which do this. There are other options, but none of them are pretty. -Kevin -- 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] run function on server restart
John D. West john.d.w...@asu.edu wrote: Sometimes the server process crashes and restarts, usually when I run some large calculations that eat up all available memory. You might want to reconfigure to avoid that. Is there any way to detect this and run a cleanup routine when it happens? What is it that are you trying to clean up? -Kevin -- 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] Windows SIngle Sign On - LINUX Server
Marcus' guide looks great. So what's the pros/cons of using the Kerberos via GSSAPI method, rather than going for the SingleSignOn method mentioned by Sunday? From: SUNDAY A. OLUTAYO [olut...@sadeeb.com] Sent: Saturday, 25 August 2012 12:00 a.m. To: Craig Ringer Cc: Jeremy Palmer; pgsql-general@postgresql.org Subject: Re: [GENERAL] Windows SIngle Sign On - LINUX Server In real world deployment, LDAP and Kerbero are often combined for authentication and authorization. The link below is a well documented howto: https://help.ubuntu.com/community/SingleSignOn Thanks, Sunday Olutayo - Original Message - From: Craig Ringer ring...@ringerc.id.au To: SUNDAY A. OLUTAYO olut...@sadeeb.com Cc: Jeremy Palmer jpal...@linz.govt.nz, pgsql-general@postgresql.org Sent: Friday, August 24, 2012 12:48:01 PM Subject: Re: [GENERAL] Windows SIngle Sign On - LINUX Server On 08/24/2012 06:10 PM, SUNDAY A. OLUTAYO wrote: LDAP will be your best choice for SSO, Ubuntu Linux can authenticate against AD. I'm not at all convinced by that. Active Directory functions as a Kerberos KDC. Kerberos provides secure authentication and (unlike LDAP) single sign-on. http://technet.microsoft.com/en-us/library/bb742516.aspx Use Kerberos via GSSAPI. Here's a good starting point by Marcus: http://www.hagander.net/talks/Deploying%20PostgreSQL%20in%20a%20Windows%20Enterprise.pdf -- Craig Ringer This message contains information, which is confidential and may be subject to legal privilege. If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify us immediately (Phone 0800 665 463 or i...@linz.govt.nz) and destroy the original message. LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ. Thank You. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Does continue in a loop not commit any changes
Greetings all, Having an issue with the pl/pgsql function below. I may or may not make an update to a table in the first IF statement. However, whenever an update is made, if the loop is continued then the update is not committed. Is it that whenever a loop is not completed rows aren't written? Didn't see anything in the docs that would suggest that so I think that I am missing something very obvious. Thanks, Rhys CREATE OR REPLACE FUNCTION netone.flow_(origin integer) RETURNS void AS $BODY$ DECLARE lr record; nextid integer; nextgeom geometry; l2 record; BEGIN create temporary table if not exists pointhold(id integer) on commit drop; create temporary table if not exists linehold(id integer) on commit drop; RAISE NOTICE 'STARTING AT %', origin; for lr in SELECT gnid, id,a.geom as ag, b.geom as bg from netone.points a, netone.lines b where gnid = origin AND st_intersects(a.geom,b.geom) /*AND id NOT IN (select id from linehold)*/ LOOP RAISE NOTICE 'LINE # %', lr.id; IF st_intersects(st_startpoint(lr.bg),lr.ag) THEN / statement where table may or may no be updated/ raise notice 'reversed'; update netone.lines set geom = st_reverse(geom) where id = lr.id; END IF; insert into pointhold (id) values (origin); insert into linehold (id) values (lr.id); select into l2 gnid, status from netone.points where st_intersects(lr.bg, geom) AND gnid not in (select id from pointhold); CONTINUE WHEN l2.status = 'OPENED' /*** once this is true the previous update is not committed ***/ PERFORM netone.flow_(l2.gnid); END LOOP; END; $BODY$ LANGUAGE plpgsql -- 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] Cannot Run EnterpriseDB Postgresql Installation
Hi, I solved it. I had created a directory junction in default downloads location to another directory in another drive. The installer was running from there and giving this error. I just remembered and though to check it from normal directory and yup, installer was running and installed fine and tested okay. So, your installer does not run though directory junction points in ntfs. There was nothing about this anywhere. Please make note of this somewhere. Thanks for your time. On Thu, Aug 23, 2012 at 11:40 PM, javad M mjavad...@gmail.com wrote: One more thing i forgot to add The console - help - about window reads Tcl for Windows Tcl 8.5.9 Tk 8.5.9 So i searched and tried downloading and running the latest Tcl available from http://www.activestate.com/activetcl And what do you know, the same thing as in the screenshot running that tcl setup Maybe some issue with the tcl in the postgresql setup I tried older postgresql setup version like 9.0.9, 9.1.4, x64 setups but same issue. For some reason the tcl component is creating some issue, Javad On Thu, Aug 23, 2012 at 11:32 PM, javad M mjavad...@gmail.com wrote: i have attached the screenshot. I searched everywhere for any log file. but nothing. On Thu, Aug 23, 2012 at 9:10 PM, Sachin Srivastava sachin.srivast...@enterprisedb.com wrote: Hello, Can we get a screenshot? Also you can check for any partial installation logs in your %TEMP% as install-postgresql.log or bitrock_installer_.log. Check the %TEMP% of the Administrator as well (If you dont see any logs in the %TEMP% of the logged in user) On Thu, Aug 23, 2012 at 8:01 PM, javad M mjavad...@gmail.com wrote: Hi, i just formatted my machine and installed fresh win7 x64. Also installed VS2012 since i do .net developement. In backend i use postgresql so downloaded latest postgresql 9.1.5 installation. But, i am not able to install. Upon executing file postgresql-9.1.5-1-windows.exe it asks for UAC and i say yes then i get a black window and another window named console with (Downloads) 1 % written in it. I have installed old version before and also on many clients but this is first time i am encountering this issue. There are also no logs anywhere. Please help, as my development is fully stranded because of this issue. -- Regards, Sachin Srivastava EnterpriseDB, India
Re: [GENERAL] run function on server restart
I have various background processes outside of postgres that need to be killed and restarted after the server reboots. -- John On Fri, Aug 24, 2012 at 2:29 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: John D. West john.d.w...@asu.edu wrote: Sometimes the server process crashes and restarts, usually when I run some large calculations that eat up all available memory. You might want to reconfigure to avoid that. Is there any way to detect this and run a cleanup routine when it happens? What is it that are you trying to clean up? -Kevin
Re: [GENERAL] run function on server restart
On 08/24/2012 03:46 PM, John D. West wrote: I have various background processes outside of postgres that need to be killed and restarted after the server reboots. -- John On Fri, Aug 24, 2012 at 2:29 PM, Kevin Grittner kevin.gritt...@wicourts.gov mailto:kevin.gritt...@wicourts.gov wrote: John D. West john.d.w...@asu.edu mailto:john.d.w...@asu.edu wrote: Sometimes the server process crashes and restarts, usually when I run some large calculations that eat up all available memory. You might want to reconfigure to avoid that. Is there any way to detect this and run a cleanup routine when it happens? What is it that are you trying to clean up? -Kevin Seems it would be the responsibility of the dependent processes to recognize pg went away, and clean themselves up (as only they know how to do). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] fast-archiver tool, useful for pgsql DB backups
Hi pgsql-general, Has anyone else ever noticed how slow it can be to rsync or tar a pgdata directory with hundreds of thousands or millions of files? I thought this could be done faster with a bit of concurrency, so I wrote a little tool called fast-archiver to do so. My employer (Replicon) has allowed me to release this tool under an open source license, so I wanted to share it with everyone. fast-archiver is written in Go, and makes uses of Go's awesome concurrency capabilities to read and write files in parallel. When you've got lots of small files, this makes a big throughput improvement. For a 90GB PostgreSQL database with over 2,000,000 data files, fast-archiver can create an archive in 27 minutes, as compared to tar in 1hr 23 min. Piped over an ssh connection, fast-archiver can transfer and write the same dataset on a gigabit network in 1hr 20min, as compared to rsync taking 3hrs for the same transfer. fast-archiver is available at GitHub: https://github.com/replicon/fast-archiver I hope this is useful to others. :-) Mathieu $ time fast-archiver -c -o /dev/null /db/data skipping symbolic link /db/data/pg_xlog 1008.92user 663.00system 27:38.27elapsed 100%CPU (0avgtext+0avgdata 24352maxresident)k 0inputs+0outputs (0major+1732minor)pagefaults 0swaps $ time tar -cf - /db/data | cat /dev/null tar: Removing leading `/' from member names tar: /db/data/base/16408/12445.2: file changed as we read it tar: /db/data/base/16408/12464: file changed as we read it 32.68user 375.19system 1:23:23elapsed 8%CPU (0avgtext+0avgdata 81744maxresident)k 0inputs+0outputs (0major+5163minor)pagefaults 0swaps
Re: [GENERAL] run function on server restart
John D. West john.d.w...@asu.edu wrote: I have various background processes outside of postgres that need to be killed and restarted after the server reboots. All of our applications are coded such that when they have an error on a database connection, they check for a serialization failure or a broken connection; if they find either they retry the database transaction. That would be my first recommendation. (Well, second, after identifying the causes of server crashes and fixing them.) Are you running any monitoring applications which could trigger what you want? -Kevin -- 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] run function on server restart
I *think* my independent processes are cleaning up in that they supposedly abort themselves if they lose db connection, but on restart there is a table of pid's I'd like to (1) make sure all of the processes are really dead, killing any who aren't (2) reset flags in a table showing the status of those process, and (3) clear out the pid list so I can restart the processes. Apparently the answer to my original question is no, there is no way to run a function at postgres startup. The question on monitoring apps intrigues me, however. What can they do, and what is available? Thanks! -- John On Fri, Aug 24, 2012 at 3:56 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: John D. West john.d.w...@asu.edu wrote: I have various background processes outside of postgres that need to be killed and restarted after the server reboots. All of our applications are coded such that when they have an error on a database connection, they check for a serialization failure or a broken connection; if they find either they retry the database transaction. That would be my first recommendation. (Well, second, after identifying the causes of server crashes and fixing them.) Are you running any monitoring applications which could trigger what you want? -Kevin
