[GENERAL] Implement online database using Postgresql
Hi, We are using PostgreSQL Database for our Desktop application using VB.Net. It works well. Now we are going to develop online application. We are new to online application. So please guide me, What should do to implement online postgresql database? Can we use VPS to create online databse? or any other way to implement it? Kindly give guidance to me. Regards Gloier
[GENERAL] if-clause to an exiting statement
hi i am a newbie to sql statments , I am running postgres 8.1 with application called opennms version 1.8.5 due to an application bug queries that I execute aginst the DB which returns raw-count=0 are being ignored and will not process a certain trigger I need to process. My question is : Can I use an if-clause into my statement (see below) which will check if the returned raw-count =0 then will return raw-count=1 ? -- SELECT a.eventuei AS _eventuei, a.nodeid AS _nodeid, a.ipaddr AS _ipaddr, now() AS _ts FROM events a WHERE a.eventuei='uei.opennms.org/comns/backup-success-trap' AND (eventcreatetime gt; now() - interval '10 minutes') -- 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] if-clause to an exiting statement
lookup CASE WHEN END in docs. -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Copying entire tsv record (from file) into a single field
I would like to use copy to populate a single row in table with data from a tsv file for further transformations. I seem not find a way to stop copy from seeing that the tsv file does indeed contain fields. This my current query COPY raw_data ( raw_record ) FROM '/tmp/some.tsv' ; Allan. -- 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] Copying entire tsv record (from file) into a single field
On 07/12/2010 11:07, Allan Kamau wrote: I would like to use copy to populate a single row in table with data from a tsv file for further transformations. I seem not find a way to stop copy from seeing that the tsv file does indeed contain fields. This my current query COPY raw_data ( raw_record ) FROM '/tmp/some.tsv' ; You can specify the character which COPY sees as the field delimiter to be something other than a tab - maybe a comma, if there are no commas in your input: copy raw_data(raw_record) from '/tmp/some.tsv' with delimiter ','; Would that do the job? 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] Implement online database using Postgresql
Le mardi 07 décembre 2010 à 13:42 +0530, Kalai R a écrit : So please guide me, What should do to implement online postgresql database? You need a web server to generate and process html forms that display the data. One is Apache, with the right module to process your forms' data. In your case maybe this could help : http://ant.apache.org/antlibs/dotnet/ Can't help you more, as I only do Perl. There is a learning curve, but you'll gain *a lot* of power. -- Vincent Veyron http://marica.fr/ Progiciel de gestion des dossiers de contentieux et d'assurance pour le service juridique -- 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] Copying entire tsv record (from file) into a single field
On Tue, Dec 7, 2010 at 2:14 PM, Raymond O'Donnell r...@iol.ie wrote: On 07/12/2010 11:07, Allan Kamau wrote: I would like to use copy to populate a single row in table with data from a tsv file for further transformations. I seem not find a way to stop copy from seeing that the tsv file does indeed contain fields. This my current query COPY raw_data ( raw_record ) FROM '/tmp/some.tsv' ; You can specify the character which COPY sees as the field delimiter to be something other than a tab - maybe a comma, if there are no commas in your input: copy raw_data(raw_record) from '/tmp/some.tsv' with delimiter ','; Would that do the job? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie There are commas in the input, and there is no guarantee that any one single character will not appear in the input. I could appoint a character such as the comma as suggested then use sed to change all commas in the incoming data to maybe '|' (pipe character) but this it may change the semantics of the data if the incoming data does contain pipe characters delimiting comma separated lists of values all in a given logical field. Allan. -- 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] COPY FROM and INSERT INTO rules
Le lundi 06 décembre 2010 à 18:27 -0600, Sairam Krishnamurthy a écrit : You should start a new thread for this Is there a way to call a rule when I use COPY FROM instead of INSERT INTO from the doc : COPY FROM will invoke any triggers and check constraints on the destination table. However, it will not invoke rules. http://www.postgresql.org/docs/9.0/static/sql-copy.html -- Vincent Veyron http://marica.fr/ Progiciel de gestion des dossiers de contentieux et d'assurance pour le service juridique -- 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] Do we want SYNONYMS?
Tom Lane wrote: Taken at face value from a Postgres perspective, these statements seem to imply that different ownership and permissions apply to a synonym than to its referenced object; which seems like a completely horrid idea from a security standpoint. But maybe they are only trying to say that a synonym hides which *schema* the referenced object is in, and that is tantamount to hiding the owner if you have the mindset that owner == schema. Can anyone elucidate on just what is behind those statements? From http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authorization .htm#i1009141 [quote] A schema object and its synonym are equivalent with respect to privileges. That is, the object privileges granted on a table, view, sequence, procedure, function, or package apply whether referencing the base object by name or by using a synonym. [/quote] ... [quote] If you grant object privileges on a table, view, sequence, procedure, function, or package by referring to the object through a synonym for the object, then the effect is the same as if no synonym were used. [/quote] Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- 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] Do we want SYNONYMS?
Hey Daniel, Again link to oracle.com... During this thread I believe that synonyms gives nothing except confusion and mess. 2010/12/7 Daniel Verite dan...@manitou-mail.org Tom Lane wrote: Taken at face value from a Postgres perspective, these statements seem to imply that different ownership and permissions apply to a synonym than to its referenced object; which seems like a completely horrid idea from a security standpoint. But maybe they are only trying to say that a synonym hides which *schema* the referenced object is in, and that is tantamount to hiding the owner if you have the mindset that owner == schema. Can anyone elucidate on just what is behind those statements? From http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authorization .htm#i1009141http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authorization%0A.htm#i1009141 [quote] A schema object and its synonym are equivalent with respect to privileges. That is, the object privileges granted on a table, view, sequence, procedure, function, or package apply whether referencing the base object by name or by using a synonym. [/quote] ... [quote] If you grant object privileges on a table, view, sequence, procedure, function, or package by referring to the object through a synonym for the object, then the effect is the same as if no synonym were used. [/quote] Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- // Dmitriy.
Re: [GENERAL] Do we want SYNONYMS?
On Mon, Dec 6, 2010 at 2:31 PM, Joshua D. Drake j...@commandprompt.com wrote: Command Prompt is currently considering writing a patch to provide synonyms to PostgreSQL. Is this something the community is interested in? Do we have use cases for it? MSSQL, DB2 and Oracle support them. I must be missing something, but really, what's the point of synonyms? What's the real-world use case for them? -- 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] Do we want SYNONYMS?
On Mon, Dec 6, 2010 at 4:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: [ shrug... ] Beauty is in the eye of the beholder, I guess. To me the search_path change seems like the natural way to do that, and flipping a mess of synonyms the hack. What happens when you miss one synonym? That's exactly what I thought when I read it, too. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Hanging with pg_restore and large objects
Hi, everyone. I'm working on a project that is using 8.3.0; among other things, I'm helping them to move to 9.0. The project is running on Windows XP. Someone from this project asked me earlier today why a particular database restore was taking a long time. How long? Well, it has been running for 1.5 days (yes, that's 36 hours). The restore is running under Windows XP, and the backup was done using pg_dump into the custom/binary format. The dumpfile was about 140 MB in size. Using the Windows process monitor, we saw that pg_restore was using about 50 percent of the CPU, doing an enormous (about 60 billion, by this point) reads from the disk, but zero writes. The dumpfile does contain a number of large (binary) objects, as well as a number of regular tables with integer and textual content. The restore was run with the -a (data only) flag, on an empty database schema. We tried to replicate this problem on another, similarly equipped machine, adding the -c (clean before restoring), -e (exit upon error), and -v (verbose) flags. We saw that the restore hung (for about 30 minutes, as of this writing) while loading one of the large objects from the restore. We tried to use pg_restore on the dumpfile, but found that it hung when restoring the same large object. It's not even close to the first large object, and I don't believe that it's the last one, either. My guess is pg_dump in 8.3 is somehow causing a problem in the dumpfile on or around that large object. So: * Is this a known problem on PostgreSQL 8.3, Windows, or the combination? * Is there an easy way to identify problems, corruption, and the like in our pg_dump file? * Should we be using a different type of dumpfile, such as text, to get around this problem for now? * Is there any obvious way to diagnose or work around this problem? * I don't believe that there's a way to tell either pg_dump or pg_restore to ignore objects with particular OIDs. Am I right? Thanks in advance for any help you can offer, Reuven -- Reuven M. Lerner -- Web development, consulting, and training Mobile: +972-54-496-8405 * US phone: 847-230-9795 Skype/AIM: reuvenlerner
Re: [GENERAL] Do we want SYNONYMS?
Vick Khera wrote: On Mon, Dec 6, 2010 at 2:31 PM, Joshua D. Drake j...@commandprompt.com wrote: Command Prompt is currently considering writing a patch to provide synonyms to PostgreSQL. Is this something the community is interested in? Do we have use cases for it? MSSQL, DB2 and Oracle support them. I must be missing something, but really, what's the point of synonyms? What's the real-world use case for them? It's about decoupling the name from the actual object, much like what soft links are for file systems. It's convenient when you need to change the underlying object without touching the application code. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- 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] Do we want SYNONYMS?
I won't press the issue for Postgres any further, but I will attest that synonyms work quite elegantly in Oracle, provide valuable functionality, and do not generally sow confusion among skilled developers. It sounds like the proposed synonym feature for Postgres perhaps had a different intention than I assumed, however, especially due to the differences between the Oracle and PG viz. how users, schemas and databases work. Thanks. /mcr -- 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] Do we want SYNONYMS?
Ack, I misspoke in my example last night about our use-case for synonyms: we would ust them for trans-*schema* object referencing, not trans-*database*. Sorry about that--I fear that may have caused more confusion than necessary. /m -- 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] if-clause to an exiting statement
hi thanks for the reply I did look at the CASE statement however cannot seem to alter the returned row-count ... CASE WHEN (@@ROW-COUNT = 0) THEN [what do I write here?] @@ROW-COUNT = 1? END -- View this message in context: http://postgresql.1045698.n5.nabble.com/if-clause-to-an-exiting-statement-tp3295519p3295641.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] SELECT is immediate but the UPDATE takes forever
Hi, I want to understand why one of my postgresql functions takes an eternity to finish. Here's an example: UPDATE comptes SET belongs_to_compte_id=42009 WHERE (codi_compte LIKE '1%' AND empresa_id=2 AND nivell=11); // takes forever to finish QUERY PLAN Seq Scan on comptes (cost=0.00..6559.28 rows=18 width=81) Filter: (((codi_compte)::text ~~ '1%'::text) AND (empresa_id = 2) AND (nivell = 11)) (2 rows) but the same SELECT count, it's immediate: SELECT count(id) FROM comptes WHERE codi_compte LIKE '1%' AND empresa_id=2 AND nivell=11; what I'm doing wrong ? thanks, regards, r. -- 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] SELECT is immediate but the UPDATE takes forever
Quoting Raimon Fernandez co...@montx.com: I want to understand why one of my postgresql functions takes an eternity to finish. Here's an example: UPDATE comptes SET belongs_to_compte_id=42009 WHERE (codi_compte LIKE '1%' AND empresa_id=2 AND nivell=11); // takes forever to finish [...] but the same SELECT count, it's immediate: SELECT count(id) FROM comptes WHERE codi_compte LIKE '1%' AND empresa_id=2 AND nivell=11; Maybe there is any check or constraint on belongs_to_compte_id.comptes that might take longer? Cheers, -Mike -- Michał Roszka m...@if-then-else.pl -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] dotted quad netmask conversion
Hello All, I am working with a postgresql database that has two columns. One for an ip address and another for the netmask. Both of these columns are char varying(30). I would like to convert to just one column as inet. Any one know a slick way to convert the dotted quad netmask, E.G. 255.255.128.0, to number of bits. I didn't see any way directly looking at the network functions in the PG documentation. Thanks in advance for your consideration. -- Stephen Clark *NetWolves* Sr. Software Engineer III Phone: 813-579-3200 Fax: 813-882-0209 Email: steve.cl...@netwolves.com http://www.netwolves.com
Re: [GENERAL] Do we want SYNONYMS?
On 12/7/2010 8:12 AM, Daniel Verite wrote: Vick Khera wrote: On Mon, Dec 6, 2010 at 2:31 PM, Joshua D. Drakej...@commandprompt.com wrote: Command Prompt is currently considering writing a patch to provide synonyms to PostgreSQL. Is this something the community is interested in? Do we have use cases for it? MSSQL, DB2 and Oracle support them. I must be missing something, but really, what's the point of synonyms? What's the real-world use case for them? It's about decoupling the name from the actual object, much like what soft links are for file systems. It's convenient when you need to change the underlying object without touching the application code. Best regards, So, you could rename a table without having to change the code? But you cant rename a column, or drop one, and thats a much more common thing I'd bet. And eventually you would change the code, right? Isn't it much better to keep everyone on the same page? If you have 10 program using 10 different names for the same table... how can that possibly be useful? Just sounds confusing and troublesome. I can see a situation for live/hot upgrades. Having old code and new code run at the same time. But eventually the old code would go away, and I think the same thing could be handled with views. (perhaps updateable view's would be required... but still) I dont see a situation where an alias gives me something updateable views dont. I'd vote we spend time on updateable views instead. And the types: table: maybe useful for live upgrade, but views, transactons and stored procs do the same thing. views: just create the new view. Have both. when the old code goes away, drop the old view. No need for an alias. sequence: why bother? Other than renaming during live upgrade, why would you need an alias? index: again, why bother... code really should not ever be dependent on an indexes name, correct? And transactions take care of live updates. So for the two use cases I've seen (live update, directing data flow (which is kinda like a live update)) we already have tools that do it: transactional ddl, views, schemas, stored procs, etc. Updateable views might be the only thing missing. Also: I wonder if it might be a bad idea. The people coming from oracle will see that PG supports synonyms, and they'll be all happy, but when they get into the guts of their translate they find PG's synonyms are different (and not compatible), and they have to throw it out and use schemas instead. On the other hand, now that I think about it, if its really easy, it might help a few people out, then why not. On the other other hand, if its not so easy, I think the time would be better spent on updatable views. So here is my new vote: IF its easy and wont slow anything down: +1 IF its hard: -1 (and spend the time on more important things) -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] restore In parallel postgres 9
Hi gurus, When I try to pg_restore -d teste -v -j 16 teste.dmp 2 teste.log I give error: pg_restore: opção inválida -- j Tente pg_restore --help para obter mais informação. How can using parallel resource in new postgres? Regards Paulo
[GENERAL] regexp err msg question
Hi: 8.3.4 on linux This query fails with this message... mydb=# select distinct fivr from alphaview where name ~ ''^foo''; ERROR: type foo does not exist (note: those are two single quotes before the ^ and after foo, NOT double quotes) Could someone explain the error message? Thanks in Advance !
Re: [GENERAL] Do we want SYNONYMS?
2010/12/7 Andy Colson a...@squeakycode.net On 12/7/2010 8:12 AM, Daniel Verite wrote: Vick Khera wrote: On Mon, Dec 6, 2010 at 2:31 PM, Joshua D. Drakej...@commandprompt.com wrote: Command Prompt is currently considering writing a patch to provide synonyms to PostgreSQL. Is this something the community is interested in? Do we have use cases for it? MSSQL, DB2 and Oracle support them. I must be missing something, but really, what's the point of synonyms? What's the real-world use case for them? It's about decoupling the name from the actual object, much like what soft links are for file systems. It's convenient when you need to change the underlying object without touching the application code. Best regards, So, you could rename a table without having to change the code? But you cant rename a column, or drop one, and thats a much more common thing I'd bet. And eventually you would change the code, right? Isn't it much better to keep everyone on the same page? If you have 10 program using 10 different names for the same table... how can that possibly be useful? Just sounds confusing and troublesome. I can see a situation for live/hot upgrades. Having old code and new code run at the same time. But eventually the old code would go away, and I think the same thing could be handled with views. (perhaps updateable view's would be required... but still) I dont see a situation where an alias gives me something updateable views dont. I'd vote we spend time on updateable views instead. And the types: table: maybe useful for live upgrade, but views, transactons and stored procs do the same thing. views: just create the new view. Have both. when the old code goes away, drop the old view. No need for an alias. sequence: why bother? Other than renaming during live upgrade, why would you need an alias? index: again, why bother... code really should not ever be dependent on an indexes name, correct? And transactions take care of live updates. So for the two use cases I've seen (live update, directing data flow (which is kinda like a live update)) we already have tools that do it: transactional ddl, views, schemas, stored procs, etc. Updateable views might be the only thing missing. Also: I wonder if it might be a bad idea. The people coming from oracle will see that PG supports synonyms, and they'll be all happy, but when they get into the guts of their translate they find PG's synonyms are different (and not compatible), and they have to throw it out and use schemas instead. On the other hand, now that I think about it, if its really easy, it might help a few people out, then why not. On the other other hand, if its not so easy, I think the time would be better spent on updatable views. So here is my new vote: IF its easy and wont slow anything down: +1 IF its hard: -1 (and spend the time on more important things) Totally agreed. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- // Dmitriy.
Re: [GENERAL] if-clause to an exiting statement
On Tuesday 07 December 2010 3:58:46 am kobi.biton wrote: hi thanks for the reply I did look at the CASE statement however cannot seem to alter the returned row-count ... CASE WHEN (@@ROW-COUNT = 0) THEN [what do I write here?] @@ROW-COUNT = 1? END -- View this message in context: http://postgresql.1045698.n5.nabble.com/if-clause-to-an-exiting-statement-t p3295519p3295641.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. See below: test= SELECT count(*) from bool_test ; count --- 33 (1 row) test= SELECT count(*) , case count(*) when 0 then 1 else count(*) end from bool_test ; count | count ---+--- 33 |33 (1 row) test= SELECT count(*) , case count(*) when 0 then 1 else count(*) end from bool_test where ifd=0; count | count ---+--- 0 | 1 (1 row) -- 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
Re: [GENERAL] regexp err msg question
Gauthier, Dave dave.gauth...@intel.com writes: mydb=# select distinct fivr from alphaview where name ~ ''^foo''; ERROR: type foo does not exist (note: those are two single quotes before the ^ and after foo, NOT double quotes) You realize of course that you've got too many quotes there. Could someone explain the error message? I think it's parsing that as name ~ '' ^ foo '' That is, empty string literal, ^ operator, typename preceding string literal, empty string literal. The typename 'string' syntax wasn't one of the SQL committee's better ideas :-( 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] dotted quad netmask conversion
On Tue, Dec 7, 2010 at 9:17 AM, Steve Clark scl...@netwolves.com wrote:
Hello All,
I am working with a postgresql database that has two columns.
One for an ip address and another for the netmask. Both of these
columns are char varying(30). I would like to convert to just
one column as inet. Any one know a slick way to convert the
dotted quad netmask, E.G. 255.255.128.0, to number of bits. I didn't
see any way directly looking at the network functions in the PG
documentation.
select '1.2.3.4'::inet '255.255.128.0'::inet;
or
select CAST('1.2.3.4' AS INET) CAST('255.255.128.0' AS INET);
Be aware that CIDR representation is not as granular as netmask.
http://www.postgresql.org/docs/8.4/interactive/functions-net.html
--
Jon
--
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] regexp err msg question
Yes, someone is using too many quotes, I think in a script because the log file gets bursts of these messages. I need to foind out who's doing this. I know pg_stat_activity is the key in that endevour, but these are remote db queries, so I have to figure out how to deal with that indirection. I was curious where the reference to type came from in the error message. Thanks Tom. -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, December 07, 2010 10:45 AM To: Gauthier, Dave Cc: pgsql-general List Subject: Re: [GENERAL] regexp err msg question Gauthier, Dave dave.gauth...@intel.com writes: mydb=# select distinct fivr from alphaview where name ~ ''^foo''; ERROR: type foo does not exist (note: those are two single quotes before the ^ and after foo, NOT double quotes) You realize of course that you've got too many quotes there. Could someone explain the error message? I think it's parsing that as name ~ '' ^ foo '' That is, empty string literal, ^ operator, typename preceding string literal, empty string literal. The typename 'string' syntax wasn't one of the SQL committee's better ideas :-( 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] dotted quad netmask conversion
On 12/07/2010 10:30 AM, Jon Nelson wrote:
On Tue, Dec 7, 2010 at 9:17 AM, Steve Clarkscl...@netwolves.com wrote:
Hello All,
I am working with a postgresql database that has two columns.
One for an ip address and another for the netmask. Both of these
columns are char varying(30). I would like to convert to just
one column as inet. Any one know a slick way to convert the
dotted quad netmask, E.G. 255.255.128.0, to number of bits. I didn't
see any way directly looking at the network functions in the PG
documentation.
select '1.2.3.4'::inet '255.255.128.0'::inet;
or
select CAST('1.2.3.4' AS INET) CAST('255.255.128.0' AS INET);
Be aware that CIDR representation is not as granular as netmask.
http://www.postgresql.org/docs/8.4/interactive/functions-net.html
Thanks for the response Jon. I should have stated this PG 8.1.x and ''
doesn't exist
for network functions.
select CAST('1.2.3.4' AS INET) CAST('255.255.128.0' AS INET);
ERROR: operator does not exist: inet inet
HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts.
--
Stephen Clark
*NetWolves*
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
http://www.netwolves.com
[GENERAL] Postgresql 9.1 pg_last_xact_replay_timestamp limitations
Hi everyone, I am very pleased to see the addition of the pg_last_xact_replay_timestamp function in Postgresql 9.1 since this, in combination with hot standby and WAL log streaming, will seriously boost the performance of our postgresql database cluster. pg_last_xact_replay_timestamp is important to us because the client application keeps a cache and makes queries with this structure: select stuff from table_name where not_modified_since $last_not_modified_since_value_we_gave_to_the_client; This way the client application only gets recent changes. $last_not_modified_since_value_we_gave_to_the_client is simply now() on master databases. In case of queries made on read-only (hot standby) databases, pg_last_xact_replay_timestamp() will be used. However, pg_last_xact_replay_timestamp() returns null when the server is restarted until a new transaction is streamed to the hot standby server. It might take a long time before this happens. Because of this, we can't rely this function completely. Since forcing an update on the master database is not a clean solution, another possibility would be to create a custom function that takes the value of pg_last_xact_replay_timestamp() and save it on disk. If the value is null (the server was restarted), we then read and return of last value stored on disk instead. Is there any better way? Also, is there any plans to make pg_last_xact_replay_timestamp() reliable even after a restart? Thank you, Gabi Julien -- 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] dotted quad netmask conversion
Steve Clark scl...@netwolves.com writes: Thanks for the response Jon. I should have stated this PG 8.1.x and '' doesn't exist for network functions. I don't think does what you want anyway. It just does a bit AND on the two addresses, it doesn't change the masklen property. There's probably only a small number of distinct netmasks you actually need to handle in this conversion. What I'd suggest is writing a simple function with a CASE statement to translate netmask to an integer mask length, and then you can use set_masklen to merge that result into the address value. 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: *****SPAM***** Re: [GENERAL] if-clause to an exiting statement
On Dec 7, 2010, at 4:58 AM, kobi.biton wrote: hi thanks for the reply I did look at the CASE statement however cannot seem to alter the returned row-count ... Well, yeah. The row count is the count of rows returned. If there are no rows matched by the query, then what exactly do you expect to happen? Set the row count to 1, so that the application then tries to access the 1st row of 0??? If you need some dummy row returned even in the case where there's no match, then you'll have to construct your query that way... -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Type cast removal - proposed exceptions for xml,enum
On tis, 2010-12-07 at 11:49 +0800, Craig Ringer wrote: This is driving me nuts when working with PgJDBC via various ORM layers (I know, I know, but they're life at this point) that would work happily with these types if they were implicitly castable to/from strings, but don't understand how to explicitly specify these postgresql-specific types when talking to the JDBC layer. Since that is a quite specific use case, why don't you add the casts yourself? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgpool slony slaves only
Hi, as the Subject says i want pgpool to talk only to my slony slaves. In my company we have a Slony Setup with 1 master replicating to 2 slaves. Our application only does read queries (never ever writes) to the 2 slaves through pgpool. While upgrading to pgpool 3.0.1 (from 2.2.5) i detected that the former admin has set up pgpool with replication_mode on. I now thought about going to master_salve_mode , but i am bit lost between the various possibilities of modes, especially cause i dont need any write queries deleivered to my slony master. cause the application we run will just read and never write. We want just connection pooling and load balacing through pgpool to our 2 slaves. Couldnt find anyone on google using pgpool to access read only his slony slaves, maybe someone has suggestions for me. Regards, Frank Jansen -- 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] dotted quad netmask conversion
On 12/07/2010 11:43 AM, Tom Lane wrote:
Steve Clarkscl...@netwolves.com writes:
Thanks for the response Jon. I should have stated this PG 8.1.x and ''
doesn't exist for network functions.
I don't think does what you want anyway. It just does a bit AND on
the two addresses, it doesn't change the masklen property.
There's probably only a small number of distinct netmasks you actually
need to handle in this conversion. What I'd suggest is writing a simple
function with a CASE statement to translate netmask to an integer mask
length, and then you can use set_masklen to merge that result into the
address value.
regards, tom lane
Googling on the net I found a couple of functions that with tweaks for 8.1
seem to work.
CREATE OR REPLACE FUNCTION inet_to_longip(v_t INET)
RETURNS BIGINT AS
$inet_to_longip$
DECLARE
t1 TEXT;
t2 TEXT;
t3 TEXT;
t4 TEXT;
i BIGINT;
BEGIN
t1 := SPLIT_PART(HOST(v_t), '.',1);
t2 := SPLIT_PART(HOST(v_t), '.',2);
t3 := SPLIT_PART(HOST(v_t), '.',3);
t4 := SPLIT_PART(HOST(v_t), '.',4);
i := (t1::BIGINT 24) + (t2::BIGINT 16) +
(t3::BIGINT 8) + t4::BIGINT;
RETURN i;
END;
$inet_to_longip$ LANGUAGE plpgsql STRICT IMMUTABLE;
CREATE OR REPLACE FUNCTION netmask_bits(v_i BIGINT)
RETURNS INTEGER AS
$netmask_msb$
DECLARE
n INTEGER;
BEGIN
n := (32-log(2, 4294967296 - v_i ))::integer;
RETURN n;
END;
$netmask_msb$ LANGUAGE plpgsql STRICT IMMUTABLE;
Which seems to do the trick.
select netmask_bits(inet_to_longip('255.255.255.0'));
netmask_bits
--
24
select netmask_bits(inet_to_longip('255.255.128.0'));
netmask_bits
--
17
Thanks all.
--
Stephen Clark
*NetWolves*
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
http://www.netwolves.com
Re: *****SPAM***** Re: [GENERAL] if-clause to an exiting statement
On Dec 7, 2010, at 9:58 AM, Kobi Biton wrote: I know it does not sound logic however I do need to set the row count to 1 in case row count returns 0 Perhaps I didn't make myself clear: you can't do that. The only thing you can do is make sure your query returns a row, and in the case where it currently doesn't return a row I have absolutely no idea what it would be that you would need to return. If it would be acceptable to always return some hard-wired dummy row in addition to the 0 or more rows that match the current query, then you could use a UNION to add the dummy row to the selection. Otherwise, perhaps the real problem is that you do not have a matching event in the database and the real solution is to add such an event. In your original post you referred to an application bug where a trigger does not run if the row count is 0. It's hard for me to imagine how it's a bug to not take action when there is no event that needs processing... -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] [ADMIN] restore In parallel postgres 9
paulo matadr saddon...@yahoo.com.br wrote: When I try to pg_restore -d teste -v -j 16 teste.dmp 2 teste.log I give error What do you get from?: pg_restore --version -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] Hanging with pg_restore and large objects
On 7 Dec 2010, at 14:51, Reuven M. Lerner wrote: My guess is pg_dump in 8.3 is somehow causing a problem in the dumpfile on or around that large object. Which version of pg_dump did you use? The one that came with the 9.0 install or the one from the old 8.3 one? It should have been the first of these two. Regardless, IMHO it shouldn't behave like it does for you now. It doesn't look like it's waiting on a lock elsewhere (Not with a CPU load like that), so it should either refuse or bail out, not enter some seemingly endless loop. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4cfe7a60802658365545708! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] is anyone from the pokertracker.com within this mailing list?
as we get more and more questions from users of their software, maybe it would be helpfull for both sides if they decide to get more involved within the PostgreSQL community. Harald -- GHUM GmbH Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 Amtsgericht Stuttgart, HRB 734971 - persuadere. et programmare
Re: [GENERAL] if-clause to an exiting statement
On 12/07/2010 08:04 AM, Kobi Biton wrote: Adrian hi, Thanks for the reply can you please show me how to incorporate the below into my below statement ? SELECT a.eventuei AS _eventuei, a.nodeid AS _nodeid, a.ipaddr AS _ipaddr, now() AS _ts FROM events a WHERE eventuei='uei.opennms.org/comns/backup-success-trap' AND (eventcreatetimegt; now() - interval '10 minutes') Thanks! Kobi Not sure this is what you want but here, reminder count(*) can have performance issues for large values of count(): SELECT a.eventuei AS _eventuei, case count(*) when 0 then 1 else count(*) end AS _ct, a.nodeid AS _nodeid, a.ipaddr AS _ipaddr, now() AS _ts FROM events a WHERE eventuei='uei.opennms.org/comns/backup-success-trap' AND (eventcreatetimegt; now() - interval '10 minutes') -- 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
Re: [GENERAL] Do we want SYNONYMS?
On Tue, 2010-12-07 at 08:31 -0500, Vick Khera wrote: On Mon, Dec 6, 2010 at 2:31 PM, Joshua D. Drake j...@commandprompt.com wrote: Command Prompt is currently considering writing a patch to provide synonyms to PostgreSQL. Is this something the community is interested in? Do we have use cases for it? MSSQL, DB2 and Oracle support them. I must be missing something, but really, what's the point of synonyms? What's the real-world use case for them? For a PostgreSQL Person? I see no real benefit to be honest. For people coming from Oracle, DB2 or MSSQL? I see a real benefit in terms of ease of porting. I asked on the Oracle free list[1] and Synonyms are used and used a lot in Oracle. Anything we can do to help those folks run screaming from err port to PostgreSQL seems like a good idea. (Assuming we can do it reasonably) Sincerely, Joshua D. Drake 1. http://www.freelists.org/post/oracle-l/Synonyms -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] Do we want SYNONYMS?
On Tue, 2010-12-07 at 09:14 -0500, Michael C Rosenstein wrote: I won't press the issue for Postgres any further, but I will attest that synonyms work quite elegantly in Oracle, provide valuable functionality, and do not generally sow confusion among skilled developers. It sounds like the proposed synonym feature for Postgres perhaps had a different intention than I assumed, however, especially due to the differences between the Oracle and PG viz. how users, schemas and databases work. Your perception has been mirrored on the Oracle free list. Really what PostgreSQL people need to come to grips with is whether or not we want to make it easier for others to port to Pg or not. (assuming reasonableness) JD Thanks. /mcr -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Tool for data modeling and ER diagram
Hi List, What is the best tool of data modeling and ER diagram for PostgreSQL. -- Thanks Regards DJ The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, re-transmission, dissemination or other use of or taking of any action in reliance upon,this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from your computer. Microland takes all reasonable steps to ensure that its electronic communications are free from viruses. However, given Internet accessibility, the Company cannot accept liability for any virus introduced by this e-mail or any attachment and you are advised to use up-to-date virus checking software.
Re: [GENERAL] Tool for data modeling and ER diagram
Hey Jaiswal, Best is relative word. It is matter of taste. I recommend dbWrench. http://www.dbwrench.com/ 2010/12/7 Jaiswal Dhaval Sudhirkumar jaiswa...@microland.com Hi List, What is the best tool of data modeling and ER diagram for PostgreSQL. -- Thanks Regards DJ The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, re-transmission, dissemination or other use of or taking of any action in reliance upon,this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from your computer. Microland takes all reasonable steps to ensure that its electronic communications are free from viruses. However, given Internet accessibility, the Company cannot accept liability for any virus introduced by this e-mail or any attachment and you are advised to use up-to-date virus checking software. -- // Dmitriy.
Re: [GENERAL] Tool for data modeling and ER diagram
On Dec 7, 2010, at 10:39 AM, Jaiswal Dhaval Sudhirkumar wrote: Hi List, What is the best tool of data modeling and ER diagram for PostgreSQL. http://wiki.postgresql.org/wiki/GUI_Database_Design_Tools
Re: [GENERAL] Do we want SYNONYMS?
Hello 2010/12/7 Joshua D. Drake j...@commandprompt.com: On Tue, 2010-12-07 at 09:14 -0500, Michael C Rosenstein wrote: I won't press the issue for Postgres any further, but I will attest that synonyms work quite elegantly in Oracle, provide valuable functionality, and do not generally sow confusion among skilled developers. It sounds like the proposed synonym feature for Postgres perhaps had a different intention than I assumed, however, especially due to the differences between the Oracle and PG viz. how users, schemas and databases work. Your perception has been mirrored on the Oracle free list. Really what PostgreSQL people need to come to grips with is whether or not we want to make it easier for others to port to Pg or not. (assuming reasonableness) it's question if this is task more for EnterpriseDB and less for PostgreSQL? Pavel JD Thanks. /mcr -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] Do we want SYNONYMS?
Joshua D. Drake j...@commandprompt.com writes: On Tue, 2010-12-07 at 08:31 -0500, Vick Khera wrote: I must be missing something, but really, what's the point of synonyms? What's the real-world use case for them? For a PostgreSQL Person? I see no real benefit to be honest. For people coming from Oracle, DB2 or MSSQL? I see a real benefit in terms of ease of porting. They're only going to make it easier to port if we cover *all* the functionality of Oracle synonyms, with *exactly* the same behavior. Otherwise this is just an advertising stunt ... 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] Tool for data modeling and ER diagram
What is the best tool of data modeling and ER diagram for PostgreSQL. http://wiki.postgresql.org/wiki/GUI_Database_Design_Tools Although it may not be considered a tool for use in the design (per se) of a database, I would highly recommend that SchemaSpy (http://schemaspy.sourceforge.net/) be added to the list. -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- 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] Do we want SYNONYMS?
On 12/07/2010 10:45 AM, Joshua D. Drake wrote: On Tue, 2010-12-07 at 08:31 -0500, Vick Khera wrote: On Mon, Dec 6, 2010 at 2:31 PM, Joshua D. Drakej...@commandprompt.com wrote: Command Prompt is currently considering writing a patch to provide synonyms to PostgreSQL. Is this something the community is interested in? Do we have use cases for it? MSSQL, DB2 and Oracle support them. I must be missing something, but really, what's the point of synonyms? What's the real-world use case for them? For a PostgreSQL Person? I see no real benefit to be honest. For people coming from Oracle, DB2 or MSSQL? I see a real benefit in terms of ease of porting. I asked on the Oracle free list[1] and Synonyms are used and used a lot in Oracle. Anything we can do to help those folks run screaming from err port to PostgreSQL seems like a good idea. (Assuming we can do it reasonably) Sincerely, Joshua D. Drake If I am following this thread correctly the biggest issue to date is getting an apple to apple comparison. The confusion seems to be that what is proposed for SYNONYMS in Pg is not actually a synonym for SYNONYMS in Oracle. -- 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
Re: [GENERAL] SELECT is immediate but the UPDATE takes forever
On 7 Dec 2010, at 15:45, Michał Roszka wrote: but the same SELECT count, it's immediate: SELECT count(id) FROM comptes WHERE codi_compte LIKE '1%' AND empresa_id=2 AND nivell=11; Maybe there is any check or constraint on belongs_to_compte_id.comptes that might take longer? Or a foreign key constraint or an update trigger, to name a few other possibilities. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4cfe7af5802659106873227! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: *****SPAM***** Re: [GENERAL] if-clause to an exiting statement
I know it does not make sens application bug however consider the following scenarion , looking at the Statement I sent I would like to check if over the last 10 minutes a certain type of event was logged and if NOT (row-count=0) then I would like to trigger and action. hope it makes more sense. Kobi. On Tue, 2010-12-07 at 10:44 -0700, Scott Ribe wrote: On Dec 7, 2010, at 9:58 AM, Kobi Biton wrote: I know it does not sound logic however I do need to set the row count to 1 in case row count returns 0 Perhaps I didn't make myself clear: you can't do that. The only thing you can do is make sure your query returns a row, and in the case where it currently doesn't return a row I have absolutely no idea what it would be that you would need to return. If it would be acceptable to always return some hard-wired dummy row in addition to the 0 or more rows that match the current query, then you could use a UNION to add the dummy row to the selection. Otherwise, perhaps the real problem is that you do not have a matching event in the database and the real solution is to add such an event. In your original post you referred to an application bug where a trigger does not run if the row count is 0. It's hard for me to imagine how it's a bug to not take action when there is no event that needs processing... -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Kobi Biton Com N S Ltd. Mobile: +972 (54) 8017668 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: *****SPAM***** Re: [GENERAL] if-clause to an exiting statement
Scott hi, I know it does not sound logic however I do need to set the row count to 1 in case row count returns 0 , can you show how to add that case clause and dummy line in my below code ? SELECT a.eventuei AS _eventuei, a.nodeid AS _nodeid, a.ipaddr AS _ipaddr, now() AS _ts FROM events a WHERE a.eventuei='uei.opennms.org/comns/backup-success-trap' AND (eventcreatetime gt; now() - interval '10 minutes') Thanks Kobi On Tue, 2010-12-07 at 09:51 -0700, Scott Ribe wrote: On Dec 7, 2010, at 4:58 AM, kobi.biton wrote: hi thanks for the reply I did look at the CASE statement however cannot seem to alter the returned row-count ... Well, yeah. The row count is the count of rows returned. If there are no rows matched by the query, then what exactly do you expect to happen? Set the row count to 1, so that the application then tries to access the 1st row of 0??? If you need some dummy row returned even in the case where there's no match, then you'll have to construct your query that way... -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Kobi Biton Com N S Ltd. Mobile: +972 (54) 8017668 -- 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] if-clause to an exiting statement
Adrian hi, Thanks for the reply can you please show me how to incorporate the below into my below statement ? SELECT a.eventuei AS _eventuei, a.nodeid AS _nodeid, a.ipaddr AS _ipaddr, now() AS _ts FROM events a WHERE eventuei='uei.opennms.org/comns/backup-success-trap' AND (eventcreatetime gt; now() - interval '10 minutes') Thanks! Kobi On Tue, 2010-12-07 at 07:36 -0800, Adrian Klaver wrote: On Tuesday 07 December 2010 3:58:46 am kobi.biton wrote: hi thanks for the reply I did look at the CASE statement however cannot seem to alter the returned row-count ... CASE WHEN (@@ROW-COUNT = 0) THEN [what do I write here?] @@ROW-COUNT = 1? END -- View this message in context: http://postgresql.1045698.n5.nabble.com/if-clause-to-an-exiting-statement-t p3295519p3295641.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. See below: test= SELECT count(*) from bool_test ; count --- 33 (1 row) test= SELECT count(*) , case count(*) when 0 then 1 else count(*) end from bool_test ; count | count ---+--- 33 |33 (1 row) test= SELECT count(*) , case count(*) when 0 then 1 else count(*) end from bool_test where ifd=0; count | count ---+--- 0 | 1 (1 row) -- Adrian Klaver adrian.kla...@gmail.com -- Kobi Biton Com N S Ltd. Mobile: +972 (54) 8017668 -- 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] SELECT is immediate but the UPDATE takes forever
=?utf-8?b?TWljaGHFgg==?= Roszka m...@if-then-else.pl writes: Quoting Raimon Fernandez co...@montx.com: I want to understand why one of my postgresql functions takes an eternity to finish. Maybe there is any check or constraint on belongs_to_compte_id.comptes that might take longer? Or maybe the UPDATE is blocked on a lock ... did you look into pg_stat_activity or pg_locks to check? 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] SELECT is immediate but the UPDATE takes forever
On 7dic, 2010, at 15:45 , Michał Roszka wrote: Quoting Raimon Fernandez co...@montx.com: I want to understand why one of my postgresql functions takes an eternity to finish. Here's an example: UPDATE comptes SET belongs_to_compte_id=42009 WHERE (codi_compte LIKE '1%' AND empresa_id=2 AND nivell=11); // takes forever to finish [...] but the same SELECT count, it's immediate: SELECT count(id) FROM comptes WHERE codi_compte LIKE '1%' AND empresa_id=2 AND nivell=11; Maybe there is any check or constraint on belongs_to_compte_id.comptes that might take longer? no, there's no check or constraint (no foreign key, ...) on this field. I'm using now another database with same structure and data and the delay doesn't exist there, there must be something wrong in my current development database. I'm checking this now ... thanks, r. Cheers, -Mike -- Michał Roszka m...@if-then-else.pl -- 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] Do we want SYNONYMS?
On 12/7/2010 1:07 PM, Adrian Klaver wrote: On 12/07/2010 10:45 AM, Joshua D. Drake wrote: On Tue, 2010-12-07 at 08:31 -0500, Vick Khera wrote: On Mon, Dec 6, 2010 at 2:31 PM, Joshua D. Drakej...@commandprompt.com wrote: Command Prompt is currently considering writing a patch to provide synonyms to PostgreSQL. Is this something the community is interested in? Do we have use cases for it? MSSQL, DB2 and Oracle support them. I must be missing something, but really, what's the point of synonyms? What's the real-world use case for them? For a PostgreSQL Person? I see no real benefit to be honest. For people coming from Oracle, DB2 or MSSQL? I see a real benefit in terms of ease of porting. I asked on the Oracle free list[1] and Synonyms are used and used a lot in Oracle. Anything we can do to help those folks run screaming from err port to PostgreSQL seems like a good idea. (Assuming we can do it reasonably) Sincerely, Joshua D. Drake If I am following this thread correctly the biggest issue to date is getting an apple to apple comparison. The confusion seems to be that what is proposed for SYNONYMS in Pg is not actually a synonym for SYNONYMS in Oracle. :-) I see what you did there! I think it covers parts. In both you can create an alias to a table, both of which you can fire off insert/update/delete. I assume in PG you could have different permissions for the table and the alias, which I assume you can do in oracle. If we pretend oracle and PG both have the same thing as a schema, and using PG's definition of schema: I assume in oracle you can create table synonym schemaA.bob for schemaB.tablex And I assume you could do the same in PG. However beyond that, I dont know what oracle supports that we'd need. (need, as in, oracle synonyms between different database instances on different computers is not going to happen.) -Andy -- 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] Hanging with pg_restore and large objects
Reuven M. Lerner reu...@lerner.co.il writes: Using the Windows process monitor, we saw that pg_restore was using about 50 percent of the CPU, doing an enormous (about 60 billion, by this point) reads from the disk, but zero writes. The dumpfile does contain a number of large (binary) objects, as well as a number of regular tables with integer and textual content. The restore was run with the -a (data only) flag, on an empty database schema. That's pretty curious. Can you take the dump file to a non-Windows machine, or at least one with a different build of pg_restore, and see what happens there? I'm wondering about possible corrupted executable, buggy zlib, etc. * Is this a known problem on PostgreSQL 8.3, Windows, or the combination? No, not that I've heard of. The most likely theory seems to be that the dump file is corrupt somehow. * Is there any obvious way to diagnose or work around this problem? Well, it'd be interesting to trace through it with a debugger. Ideally you shouldn't get an infinite loop (as this seems to be) even with corrupt input. Is the data sufficiently non-proprietary that you'd be willing to show the dump file to someone else? * I don't believe that there's a way to tell either pg_dump or pg_restore to ignore objects with particular OIDs. Am I right? Try using pg_restore -l, edit the list file, pg_restore -L. That would let you get the remaining data out, and would also give evidence of whether there is corruption in the structure of the dump file or just in the data of the one BLOB. 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] Do we want SYNONYMS?
On Tue, 2010-12-07 at 19:54 +0100, Pavel Stehule wrote: Hello 2010/12/7 Joshua D. Drake j...@commandprompt.com: On Tue, 2010-12-07 at 09:14 -0500, Michael C Rosenstein wrote: I won't press the issue for Postgres any further, but I will attest that synonyms work quite elegantly in Oracle, provide valuable functionality, and do not generally sow confusion among skilled developers. It sounds like the proposed synonym feature for Postgres perhaps had a different intention than I assumed, however, especially due to the differences between the Oracle and PG viz. how users, schemas and databases work. Your perception has been mirrored on the Oracle free list. Really what PostgreSQL people need to come to grips with is whether or not we want to make it easier for others to port to Pg or not. (assuming reasonableness) it's question if this is task more for EnterpriseDB and less for PostgreSQL? Well no I don't think that is a valid question honestly. EDB Advanced server is a proprietary product that has zero standing with the community direction. That is not a negative remark on EDB or Advanced server just that it really isn't our concern. JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] Do we want SYNONYMS?
On Tue, Dec 7, 2010 at 1:54 PM, Pavel Stehule pavel.steh...@gmail.comwrote:
Hello
2010/12/7 Joshua D. Drake j...@commandprompt.com:
Your perception has been mirrored on the Oracle free list. Really what
PostgreSQL people need to come to grips with is whether or not we want
to make it easier for others to port to Pg or not. (assuming
reasonableness)
it's question if this is task more for EnterpriseDB and less for
PostgreSQL?
FWIW, EnterpriseDB Advanced Server has had the SYNONYM feature for quite a
while now: http://www.enterprisedb.com/documentation/ddl-synonims.html
Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
Re: [GENERAL] Tool for data modeling and ER diagram
gwch...@gmail.com (Gary Chambers) writes: What is the best tool of data modeling and ER diagram for PostgreSQL. http://wiki.postgresql.org/wiki/GUI_Database_Design_Tools Although it may not be considered a tool for use in the design (per se) of a database, I would highly recommend that SchemaSpy (http://schemaspy.sourceforge.net/) be added to the list. I have added TCM to the list... http://wwwhome.cs.utwente.nl/~tcm/ I use it quite a lot when designing things. It makes no attempt to do automagic mapping of diagrams onto physical implementation, but when trying to deal with reasonably deep modelling issues (e.g. - trying to figure out what are the entities and what are the relationships between them), the full detail of schemas of tables seem like a distraction. -- let name=cbbrowne and tld=gmail.com in name ^ @ ^ tld;; http://linuxdatabases.info/info/slony.html Rules of the Evil Overlord #2. My ventilation ducts will be too small to crawl through. http://www.eviloverlord.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] Hanging with pg_restore and large objects
Hi, everyone. Alban wrote: Which version of pg_dump did you use? The one that came with the 9.0 install or the one from the old 8.3 one? It should have been the first of these two. The dump was done by someone using the old, existing system, which runs under 8.3. She complained that she was having problems (i.e., the 36-hour restore), and after we looked at it, we decided to take the file and try to import it into our 9.0 database with pg_restore 9.0, just to see what was going on. It's quite possible that the pg_dump from 9.0 would have done a better or more intelligent job, but we can't switch it into widespread use right now. (We have 8.3 installed in a closed-box product that's physically distributed to customers.) We can mix and match 8.3 and 9.0 in the development lab, but not on a widespread scale. Reuven -- Reuven M. Lerner -- Web development, consulting, and training Mobile: +972-54-496-8405 * US phone: 847-230-9795 Skype/AIM: reuvenlerner -- 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] Hanging with pg_restore and large objects
Reuven M. Lerner reu...@lerner.co.il writes: Hi, everyone. Alban wrote: Which version of pg_dump did you use? The one that came with the 9.0 install or the one from the old 8.3 one? It should have been the first of these two. The dump was done by someone using the old, existing system, which runs under 8.3. Hmmm ... I wonder whether this is related to the known problem that 8.3's pg_dump doesn't correctly detect file seekability under Windows: http://archives.postgresql.org/pgsql-hackers/2010-06/msg01227.php That means the dump file will not contain any data offset pointers. Up to now that was only known to cause issues for parallel pg_restore, but maybe you found another case. But that's just a hypothesis, and a quick test here doesn't seem to support it: I can still do pg_restore -a from a blob-containing dump that I forced to not have data offsets. OTOH I'm not using Windows. Does it work any better if you use 9.0's pg_dump to dump from the 8.3 server? 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
[GENERAL] PG84 and SSL on CentOS-5.5 was PG84 and SELinux
I received absolutely no reply to my question on the CentOS mailing list so I have to turn to this venue again for help. I note the following things: postgresql-server.i386 8.4.4-2PGDG.el5 installed openssl.i686 0.9.8e-12.el5_4.6 installed Might there be a problem between the server being compiled for i386 and openssl for i686? I cannot for the life of me determine what configuration problem causes this error. On Fri, December 3, 2010 16:04, James B. Byrne wrote: When I try to start the server with ssl=on it fails with this error: Auto configuration failed 29006:error:0E065068:configuration file routines:STR_COPY:variable has no value:conf_def.c:629:line 207 -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] PG84 and SSL on CentOS-5.5 was PG84 and SELinux
On Tue, 2010-12-07 at 16:54 -0500, James B. Byrne wrote: I received absolutely no reply to my question on the CentOS mailing list so I have to turn to this venue again for help. I note the following things: postgresql-server.i386 8.4.4-2PGDG.el5 installed openssl.i686 0.9.8e-12.el5_4.6 installed Might there be a problem between the server being compiled for i386 and openssl for i686? I cannot for the life of me determine what configuration problem causes this error. No those lib differences are both still 32bit. You would have a problem if one was 64bit. So you should be fine there. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] PG84 and SSL on CentOS-5.5 was PG84 and SELinux
On Tue, December 7, 2010 16:56, Joshua D. Drake wrote: No those lib differences are both still 32bit. You would have a problem if one was 64bit. So you should be fine there. Joshua D. Drake Ok. How do I get postgresql to cough up more processing detail on startup? The message that I presently get makes no sense at all to me. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] PG84 and SSL on CentOS-5.5 was PG84 and SELinux
James B. Byrne byrn...@harte-lyne.ca writes: Ok. How do I get postgresql to cough up more processing detail on startup? The message that I presently get makes no sense at all to me. The message isn't coming from postgres --- it's openssl that you're wishing would be more verbose. What I'd try next is strace'ing the postmaster so you can see what happened right before the error report. With luck that will point you at a specific configuration file that's (presumably) messed up. 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
[GENERAL] pg_standby logging issues
Hi, I am using pg_standby in the recovery_command of recover.conf, and with the d option I am getting an entry every few seconds when it checks for the trigger and WAL files. If I leave out the d option, I seem to get nothing. What messages should I see without the d option? ( I want to try to keep the logging down a bit...) It also seems that I am unable to rotate the log files while pg_standby is running. If I rename the log file and immediately create it¹s replacement, all the logging stops. I have to restart pg_ctl to get the pg_standby log moving again. What workarounds would you suggest for this case? Would you anticipate any problem with scripting the logfile rotation and restart of pg_ctl on a regular basis? Anything more elegant out there? Thanks!! Greg
[GENERAL] Using PG with Windows EFS or TrueCrypt for encryption
Hi - I have searched the lists for comments about using PG with EFS and/or TrueCrypt in order to encrypt the entire database transparently. I found a few posts making reference to this possibility so I have tried them both, but I didn't get either to work. I have PG-8.3 running on Windows server 2008 (64-bit). In the first scenario I just used Windows EFS (encrypting file system) to encrypt the database OID folder in the data\ folder. After I did this, the PG service started, but I could not access the database in pgAdmin. Then I attempted to mount a normal encrypted volume with TrueCrypt, move the data\ and sub-folders to this volume and reconfigure PG to point to this as the data folder. Now, the PG service will not start at all. Has anyone implemented something like this for PG in Windows? Thanks! Brady -- Brady Mathis | bmat...@r-hsoftware.com | 877.696.6547 ext 102
[GENERAL] Uncommitted Data
Hi Everyone, What does PG do with data that has been inserted into a table, but was never committed? Does the data get discarded once the connection dies? 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] PG84 and SSL on CentOS-5.5 was PG84 and SELinux
On Tue, Dec 07, 2010 at 05:15:45PM -0500, James B. Byrne wrote: On Tue, December 7, 2010 16:56, Joshua D. Drake wrote: No those lib differences are both still 32bit. You would have a problem if one was 64bit. So you should be fine there. Joshua D. Drake Ok. How do I get postgresql to cough up more processing detail on startup? The message that I presently get makes no sense at all to me. do we know that pg was compiled with ssl? maybe a bonehead question, but low hanging fruit is my specialty. -- 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] Uncommitted Data
On Wed, 2010-12-08 at 00:56 +, Jonathan Tripathy wrote: Hi Everyone, What does PG do with data that has been inserted into a table, but was never committed? Does the data get discarded once the connection dies? Yes, the transaction will rollback. Thanks -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Abusing Postgres in fun ways.
I'm creating a data queue on top of postgres and I'm wondering if I've made an incorrect assumption about isolation or synchronization or some similar issue. Every item in the queue is given a unique ID from a sequence. CREATE TABLE data_queue ( sequence_num BIGINT PRIMARY KEY, sender_key BIGINT NOT NULL, datablob bytea ); I read from the queue by passing in the last _highest_seen_sequence_num to a stored procedure: SELECT * from data_queue WHERE sequence_num _highest_seen_sequence_num ORDER BY sequence_num ASC Obviously with readers and writers racing I need some sort of synchronization. I've found the advisory locks and those did seem to be my best bet. I used explicit locking for a while but ran into an issue with our daily backups and ACCESS_EXCLUSIVE (which I might be able to revisit) I'm also trying to create a setup where there is basically no blocking, writers can always write, readers are not blocked by writers (though there may be a delay in what is visible to the reader). Before I dump a bunch of SQL on the list, my plan in short is to stage writes to a similar table: stage_data_queue, and then copy them all into a table visible by readers. 1 Writers get a shared advisory lock, get the next sequence_num and Insert one row, then release a shared advisory lock (in one stored procedure) 2 At some point there is a 'tick' and another thread gets the corresponding exclusive advisory lock (letting all in flight writes finish). Then copy all rows into another table visible to the readers, then Truncate the staging table, and release the exclusive lock. (all in one stored procedure) My fear is that there is still a race here because the writer (1) calls unlock at the end of the stored procedure, and thus there is a window before the row is committed, and (2) may end up truncating that data... I think I could fix this by leaving the (1) shared lock locked through the end of the stored procedure, and calling back unlocking it later. I might also be able to fix this with Explicit Locks because I assume those will get properly unlocked after the Insert is truly committed. Am I on the wrong track here? -JD
Re: [GENERAL] PG84 and SSL on CentOS-5.5 was PG84 and SELinux
I have now tracked down and resolved the problem. There were clues to the solution in the error message but I lacked sufficient experience with ssl to realize it. The error was an uncommented line in /etc/pki/tls/openssl.cnf that depended upon an environment variable (ALTNAME) being set (subjectAltName=$ENV::ALTNAME). This was line 270 in that file. Note the error message: Auto configuration failed 29006:error:0E065068:configuration file routines:STR_COPY:variable has no value:conf_def.c:629:line 207 Given what I know now I infer that conf_def is the variable that holds the actual file name of whatever configuration file is passed to openssl. The error message would have been far more informative had it provided the variable value rather than the variable name. And, I have no idea why PG84 choked on this and PG81 did not. Anyway, our upgraded PG84 service is now running with ssl enabled. Many thanks for the hints and suggestions. They did in fact eventually point me in the right direction. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Service Doesn't Start
I could not get the service to start on my business Windows XP laptop where I am not an administrator. I had been running it and then it would not start. I don't know if a corporate download changed something or what. I kept getting a message from AdminIII that the server was not listening. After searching through many Windows' Wizards, windows and features I found in Control Panel | Admin Tools | Services, and then right clicking on postgresql-9.0 - PostgreSQL Server 9.0, a context menu came up. (BTW postgresql shoed up as Startup Type Automatic and it was not started.) On the context menu, I selected 'Properties' and then the 'Log On' tab. It was configured as './postgres' for 'This account'. I could not change the './' prefix. I changed the selection from 'This Account' to 'Local System account'. I was then able to start the server from both the Serves app and from AdminIII. I am listed as a user in the domain. The postgres user is listed for the machine. I hope this helps others. I hope I can find this the next time I get stuck. ray keywords: postgresql, server, services, start, fail -- 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 9.1 pg_last_xact_replay_timestamp limitations
On Wed, Dec 8, 2010 at 1:31 AM, Gabi Julien gabi.jul...@broadsign.com wrote: pg_last_xact_replay_timestamp() returns null when the server is restarted until a new transaction is streamed to the hot standby server. It might take a long time before this happens. Because of this, we can't rely this function completely. I couldn't reproduce this. Could you provide a self-contained test case? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Abusing Postgres in interesting ways
Hello all, I'm creating a data queue on top of postgres and I'm wondering if I've made an incorrect assumption about isolation or synchronization or some similar issue. Every item in the queue is given a unique ID from a sequence. CREATE TABLE data_queue ( sequence_num BIGINT PRIMARY KEY, sender_key BIGINT NOT NULL, datablob bytea ); I read from the queue by passing in the last _highest_seen_sequence_num to a stored procedure: SELECT * from data_queue WHERE sequence_num _highest_seen_sequence_num ORDER BY sequence_num ASC Obviously with readers and writers racing I need some sort of synchronization. I've found the advisory locks and those seem to be my best bet. I used explicit locking for a while but ran into an issue with our daily backups and ACCESS_EXCLUSIVE (which I might be able to revisit) I'm also trying to create a setup where there is basically no blocking, writers can always write, readers are not blocked by writers (though there may be a delay in what is visible to the reader). Before I dump a bunch of SQL on the list, my plan in short to stage writes to a similar table: stage_data_queue. 1 Writers get a shared advisory lock, Insert one row, and release shared advisory lock (in one stored procedure) 2 At some point there is a 'tick' and another thread gets the corresponding exclusive advisory lock (letting all in flight writes finish). Then copy all rows into another table visible to the readers, then Truncate the staging table, and releasing the exclusive lock. (all in one stored procedure) My fear is that there is still a race here because the writer (1) calls unlock at the end of the stored procedure, and thus there is a window before the row is committed, and (2) may end up truncating that data... I think I could fix this by leaving the (1) shared lock locked through the end of the stored procedure, and calling back unlocking it later. I might also be able to fix this with Explicit Locks because I assume those will get properly unlocked after the Insert is truly committed. Am I on the wrong track here? -JD
Re: [GENERAL] Abusing Postgres in interesting ways
On 12/07/10 6:04 PM, bubba postgres wrote: Hello all, I'm creating a data queue on top of postgres and I'm wondering if I've made an incorrect assumption about isolation or synchronization or some similar issue. Every item in the queue is given a unique ID from a sequence. CREATE TABLE data_queue ( sequence_num BIGINT PRIMARY KEY, sender_key BIGINT NOT NULL, datablob bytea ); I read from the queue by passing in the last _highest_seen_sequence_num to a stored procedure: SELECT * from data_queue WHERE sequence_num _highest_seen_sequence_num ORDER BY sequence_num ASC Obviously with readers and writers racing I need some sort of synchronization. I've found the advisory locks and those seem to be my best bet. I used explicit locking for a while but ran into an issue with our daily backups and ACCESS_EXCLUSIVE (which I might be able to revisit) I'm also trying to create a setup where there is basically no blocking, writers can always write, readers are not blocked by writers (though there may be a delay in what is visible to the reader). why don't you use a serial for your sequence_num ? a serial is a int or bigint thats connected to a sql sequence, which takes care fo all that itself, without needing any fancy nonsense. and, no, you shouldn't need any such global locking, at least as long as you only have one process Before I dump a bunch of SQL on the list, my plan in short to stage writes to a similar table: stage_data_queue. 1 Writers get a shared advisory lock, Insert one row, and release shared advisory lock (in one stored procedure) stop with all the locking. design your queries and inserts to work smoothly with MVCC and none of that should be neccessary. 2 At some point there is a 'tick' and another thread gets the corresponding exclusive advisory lock (letting all in flight writes finish). Then copy all rows into another table visible to the readers, then Truncate the staging table, and releasing the exclusive lock. (all in one stored procedure) My fear is that there is still a race here because the writer (1) calls unlock at the end of the stored procedure, and thus there is a window before the row is committed, and (2) may end up truncating that data... hard locks are generally a bad idea if they can be avoided. use transactions to group things together that have to happen atomically. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
