Re: [GENERAL] WIP: CoC V3
Sorry. I just saw the reference to this in the related thread. On Jan 11, 2016 7:01 PM, "Andy Chambers" <achambers.h...@gmail.com> wrote: > Any reason not to just adopt the contributor covenant? > > http://contributor-covenant.org/ > tl;dr; > > * Removed #6 (Social Justice) > > PostgreSQL Global Development Group (PGDG) Code of Conduct (CoC): > > 1. The CoC is to provide community guidelines for creating and enforcing a > safe, respectful, productive, and collaborative place for any person who is > willing to contribute in a safe, respectful, productive and collaborative > way. > > 2. The CoC is not about being offended. As with any diverse community, > anyone can get offended at anything. > > 3. A safe, respectful, productive and collaborative environment is free of > non-technical or personal comments, for example ones related to gender, > sexual orientation, disability, physical appearance, body size, race or > personal attacks. > > 4. Any sustained disruption of the collaborative space (mailing lists, IRC > etc..) or other PostgreSQL events shall be construed as a violation of the > CoC and appropriate action will be taken by the CoC committee. > > 5. The CoC is only about interaction with the PostgreSQL community. Your > private and public lives outside of the PostgreSQL community are your own. > > Sincerely, > > JD > > > -- > Command Prompt, Inc. http://the.postgres.company/ > +1-503-667-4564 > PostgreSQL Centered full stack support, consulting and development. > > > -- > 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] WIP: CoC V3
Any reason not to just adopt the contributor covenant? http://contributor-covenant.org/ tl;dr; * Removed #6 (Social Justice) PostgreSQL Global Development Group (PGDG) Code of Conduct (CoC): 1. The CoC is to provide community guidelines for creating and enforcing a safe, respectful, productive, and collaborative place for any person who is willing to contribute in a safe, respectful, productive and collaborative way. 2. The CoC is not about being offended. As with any diverse community, anyone can get offended at anything. 3. A safe, respectful, productive and collaborative environment is free of non-technical or personal comments, for example ones related to gender, sexual orientation, disability, physical appearance, body size, race or personal attacks. 4. Any sustained disruption of the collaborative space (mailing lists, IRC etc..) or other PostgreSQL events shall be construed as a violation of the CoC and appropriate action will be taken by the CoC committee. 5. The CoC is only about interaction with the PostgreSQL community. Your private and public lives outside of the PostgreSQL community are your own. Sincerely, JD -- Command Prompt, Inc. http://the.postgres.company/ +1-503-667-4564 PostgreSQL Centered full stack support, consulting and development. -- 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] Unit tests and foreign key constraints
On Thu, May 21, 2015 at 1:34 PM, Martijn van Oosterhout klep...@svana.org wrote: On Thu, May 21, 2015 at 12:39:01PM -0700, Andy Chambers wrote: Hey All, I've started trying to use foreign key constraints in my schema but it seems to make it more difficult to write unit tests that touch the database because each test now requires more setup data to satisfy the foreign key constraint. (I know some say your unit tests shouldn't touch the DB but the more full stack tests I have, the better I sleep at night :-)) I wondered if anyone else has run into this problem and found a good strategy to mitigate it. I thought I might be able to make these constraints deferred during a test run since I have automatic rollback after each test but even after set constraints all deferred, I still got a foreign key violation during my test run if the test tries to insert data with a non-existent foreign key. Foreign keys aren't deferrable by default, you have to create them that way... Ah that's what I was missing. Thanks! -- Andy
[GENERAL] Unit tests and foreign key constraints
Hey All, I've started trying to use foreign key constraints in my schema but it seems to make it more difficult to write unit tests that touch the database because each test now requires more setup data to satisfy the foreign key constraint. (I know some say your unit tests shouldn't touch the DB but the more full stack tests I have, the better I sleep at night :-)) I wondered if anyone else has run into this problem and found a good strategy to mitigate it. I thought I might be able to make these constraints deferred during a test run since I have automatic rollback after each test but even after set constraints all deferred, I still got a foreign key violation during my test run if the test tries to insert data with a non-existent foreign key. Cheers, Andy
[GENERAL] Testing Views
Hello All, I have a problem for which a view seems like a nice solution. Basically we want to see all records in some table that are older than 5 days and haven't yet gone through further processing. This particular view is probably simple enough that it doesn't require unit tests but I wonder how people test complex views that depend on the current date or time. I found a thread on the hackers list [1] that talked about stubbing pg_catalog.now() but was wondering if any TDD minded developers had invented anything better since then. One option I thought of was to just not use views that depend on the current date or time and instead create a set returning function that takes the time as a parameter. Would such a function have similar performance characteristics to an equivalent view? Cheers, Andy [1]: http://postgresql.nabble.com/overriding-current-timestamp-td5507701.html
[GENERAL] Sequence moves forward when failover is triggerred
Hey All, We used the linked guide to setup streaming replication. http://wiki.postgresql.org/wiki/Streaming_Replication When testing the failover procedure, we noticed that when the new master comes up, some sequences have moved forward (by between 30 and 40). I see there's a cache option when creating the sequence but we're not using that. Is this to be expected? Thanks, Andy -- Andy Chambers Software Engineer (e) achamb...@mcna.net (t) 954-682-0573 CONFIDENTIALITY NOTICE: This electronic mail may contain information that is privileged, confidential, and/or otherwise protected from disclosure to anyone other than its intended recipient(s). Any dissemination or use of this electronic mail or its contents by persons other than the intended recipient(s) is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail so that we may correct our internal records. Please then delete the original message. Thank you.
Re: [GENERAL] Do I need archive_mode = on for hot standby?
On Mon, Jul 2, 2012 at 3:33 AM, Janne H jannehso...@yahoo.com wrote: Hi there. I'm planning on setting up a master database and multiple hot standby slaves using streaming replication. If I use a large(*) value on wal_keep_segments do I really need archive_mode = on then? Any potential problems with this strategy I should be aware about? (*) With large value I mean a value such that a failed slave will be up and running again (with a few days margin) before the master server starts to clean out old WAL segments. I believe you are correct. At least that's what it says here... http://wiki.postgresql.org/wiki/Streaming_Replication -- Andy
Re: [GENERAL] Sequence moves forward when failover is triggerred
On Tue, Jul 10, 2012 at 6:48 PM, Tom Lane t...@sss.pgh.pa.us wrote: Andy Chambers achamb...@mcna.net writes: When testing the failover procedure, we noticed that when the new master comes up, some sequences have moved forward (by between 30 and 40). I see there's a cache option when creating the sequence but we're not using that. Is this to be expected? Yes. This is an artifact of an optimization that reduces the number of WAL records generated by nextval() calls --- server processes will write WAL records that say they've consumed multiple sequence values ahead of where they actually have. AFAICS this is not distinguishably different from the case where a transaction consumes that number of sequence values and then rolls back, so I don't see much wrong with that optimization. OK Cool. Thanks for confirming. I think I made a poor decision by having our application generate checkbook numbers on demand using sequences. I've since realized (due to this and other reasons like not being able to see what nextval() would return without actually moving the sequence forward) that it would probably be better to generate an entire checkbook's worth of numbers whenever the checks are physically received from the bank. Then just have the app pull the next available check. Andy
[GENERAL] Weird LIKE behaviour
Below are two queries that should be pretty much the same but with the first one, I'm trying to boil it down to a minimal test-case so I don't have to export the table definition of dcm.providers. The first one returns nothing but at least executes the query. = create table foo ( foo text ); = select * from foo where foo like 'FOO%\' The second one fails to execute the query... = select * from dcm.providers where lname like 'FOO%\' ERROR: LIKE pattern must not end with escape character Our server is 9.1.4 and can reproduce this behaviour with either 8.4 or 9.1 clients. -- Andy Chambers Software Engineer (e) achamb...@mcna.net (t) 954-682-0573 CONFIDENTIALITY NOTICE: This electronic mail may contain information that is privileged, confidential, and/or otherwise protected from disclosure to anyone other than its intended recipient(s). Any dissemination or use of this electronic mail or its contents by persons other than the intended recipient(s) is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail so that we may correct our internal records. Please then delete the original message. 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
Re: [GENERAL] Weird LIKE behaviour
On Fri, Jul 6, 2012 at 4:26 PM, David Johnston pol...@yahoo.com wrote: -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Andy Chambers Sent: Friday, July 06, 2012 4:17 PM To: pgsql Subject: [GENERAL] Weird LIKE behaviour Below are two queries that should be pretty much the same but with the first one, I'm trying to boil it down to a minimal test-case so I don't have to export the table definition of dcm.providers. The first one returns nothing but at least executes the query. = create table foo ( foo text ); = select * from foo where foo like 'FOO%\' The second one fails to execute the query... = select * from dcm.providers where lname like 'FOO%\' ERROR: LIKE pattern must not end with escape character Our server is 9.1.4 and can reproduce this behaviour with either 8.4 or 9.1 clients. The only part of the table dcm.providers that should matter is the data type of the lname column - which you have not provided. I thought it might be due to the datatype too. Originally it was character varying (30) but I alter table'd it to text to see if that was the problem. Perhaps the fact that it was originally a varchar makes the difference. I'll check that now. Thanks, 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] Cancel a pg_ctl stop
Is it possible to cancel a pg_ctl stop if some clients remain connected and there is no longer a need to stop the DB? -- Andy Chambers Software Engineer (e) achamb...@mcna.net (t) 954-682-0573 CONFIDENTIALITY NOTICE: This electronic mail may contain information that is privileged, confidential, and/or otherwise protected from disclosure to anyone other than its intended recipient(s). Any dissemination or use of this electronic mail or its contents by persons other than the intended recipient(s) is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail so that we may correct our internal records. Please then delete the original message. 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] Promotion of standby to master
I understand that it's possible to promote a hot standby pg server simply by creating the failover file. In a scenario where there are multiple standby servers, is it possible to point the other standby servers to the new master without creating a new base backup? When I tried to do this, I ran into the timeline 2 of the primary does not match recovery target timeline 1. I'm just trying to figure out if that's because the procedure I followed to promote the standby was wrong or because it's just not possible. Thanks, Andy -- Andy Chambers Software Engineer (e) achamb...@mcna.net (t) 954-682-0573 CONFIDENTIALITY NOTICE: This electronic mail may contain information that is privileged, confidential, and/or otherwise protected from disclosure to anyone other than its intended recipient(s). Any dissemination or use of this electronic mail or its contents by persons other than the intended recipient(s) is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail so that we may correct our internal records. Please then delete the original message. 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] Memory Overcommit
Hi All, We've just run into the dreaded OOM Killer. I see that on Linux 2.6, it's recommended to turn off memory overcommit. I'm trying to understand the implications of doing this. The interweb says this means that forking servers can't make use of copy on write semantics. Is this true? Does it matter for a server running just postgres? -- Andy Chambers Software Engineer (e) achamb...@mcna.net (t) 954-682-0573 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] new rows based on existing rows
Hi, I frequently have a need to insert new rows into a table that are based on existing rows but with small changes. This is easy using something like insert into foo (a,b,foo_date) select a,b,now() from foo old where returning oid However in the application layer, I need to know which new record corresponds with which original record So ideally, I'd like to be able to do insert into foo (a,b,foo_date) select a,b,now() from foo old where returning oid, old.oid ...but this doesn't work. It seems you only have access to the table being modified in a returning clause. Is there a way I can return a simple mapping between old oids and new oids as part of the statement that inserts the new ones? Cheers, Andy -- Andy Chambers Software Engineer (e) achamb...@mcna.net (t) 954-682-0573 CONFIDENTIALITY NOTICE: This electronic mail may contain information that is privileged, confidential, and/or otherwise protected from disclosure to anyone other than its intended recipient(s). Any dissemination or use of this electronic mail or its contents by persons other than the intended recipient(s) is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail so that we may correct our internal records. Please then delete the original message. 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
Re: [GENERAL] trigger when clause
On Tue, Apr 10, 2012 at 5:10 PM, Jeff Davis pg...@j-davis.com wrote: On Tue, 2012-04-10 at 16:15 -0400, Andy Chambers wrote: Does anyone know the time complexity of the algorithm used to handle triggers with a when clause? It's done with a linear scan of all triggers, testing the WHEN clause for each. To make this a little more concrete, what is likely to perform better a) A single trigger with n if/else clauses b) A set of n triggers each using a different when clause. Both are essentially linear. If you want to scale to a large number of conditions, I would recommend using one trigger in a fast procedural language, and searching for the matching conditions using something better than a linear search. To beat a linear search, you need something resembling an index, which is dependent on the types of conditions. For instance, if your conditions are: 00 = x 10 10 = x 20 20 = x 30 ... you can use a tree structure. But, obviously, postgres won't know enough about the conditions to know that a tree structure is appropriate from a given sequence of WHEN clauses. So, you should use one trigger and code the condition matching yourself. Thanks Jeff. That's very helpful. -- Andy
[GENERAL] trigger when clause
Hi, Does anyone know the time complexity of the algorithm used to handle triggers with a when clause? To make this a little more concrete, what is likely to perform better a) A single trigger with n if/else clauses b) A set of n triggers each using a different when clause. What if n is 10? What if n is 200? The when clause would just be comparing a text predicate column for equality. Cheers, Andy -- Andy Chambers Software Engineer (e) achamb...@mcna.net (t) 954-682-0573 CONFIDENTIALITY NOTICE: This electronic mail may contain information that is privileged, confidential, and/or otherwise protected from disclosure to anyone other than its intended recipient(s). Any dissemination or use of this electronic mail or its contents by persons other than the intended recipient(s) is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail so that we may correct our internal records. Please then delete the original message. Thank you.
[GENERAL] copy syntax
dcm_eob= copy ar_data from '/tmp/ar-data.csv' with header true; ERROR: syntax error at or near true LINE 1: copy ar_data from '/tmp/ar-data.csv' with header true; I can't figure out what's wrong with the syntax above. Is something missing? -- Andy Chambers Software Engineer (e) achamb...@mcna.net (t) 954-682-0573 CONFIDENTIALITY NOTICE: This electronic mail may contain information that is privileged, confidential, and/or otherwise protected from disclosure to anyone other than its intended recipient(s). Any dissemination or use of this electronic mail or its contents by persons other than the intended recipient(s) is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail so that we may correct our internal records. Please then delete the original message. 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] pg-admin development snapshots
Hi, The link[1] for the development snapshots of pg-admin as advertised here [2] seems to be broken. Are these snapshots hosted somewhere else these days or are they no longer produced. I have a colleague who's bravely switching from SQL Server to Postgresql who'd really like to use the new scripting feature. [1]: http://www.pgadmin.org/snapshots [2]: http://www.pgadmin.org/download/snapshots.php -- Andy Chambers Software Engineer (e) achamb...@mcna.net (t) 954-682-0573 CONFIDENTIALITY NOTICE: This electronic mail may contain information that is privileged, confidential, and/or otherwise protected from disclosure to anyone other than its intended recipient(s). Any dissemination or use of this electronic mail or its contents by persons other than the intended recipient(s) is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail so that we may correct our internal records. Please then delete the original message. 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
Re: [GENERAL] pg-admin development snapshots
On Tue, Mar 20, 2012 at 4:53 PM, Guillaume Lelarge guilla...@lelarge.info wrote: Hi, On Tue, 2012-03-20 at 16:01 -0400, Andy Chambers wrote: [...] The link[1] for the development snapshots of pg-admin as advertised here [2] seems to be broken. Are these snapshots hosted somewhere else these days or are they no longer produced. They are no longer produced. I'll fix the website. Thanks for noticing. I have a colleague who's bravely switching from SQL Server to Postgresql who'd really like to use the new scripting feature. Which scripting feature are you talking about? http://pgscript.projects.postgresql.org/INDEX.html Am I right in thinking this will be included in the next version of pg-admin? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] dropping an index inside a transaction
Hi All, If one drops an index inside a transaction, is that index still usable by other transactions? My use-case is loading millions of records into a heavily indexed table. I'd like to speed up the load by temporarily dropping the indices. I'm wondering if I can do this inside a transaction so that the rest of the application can still benefit from the indices. Cheers, Andy -- Andy Chambers Software Engineer (e) achamb...@mcna.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] CROSS JOIN performance
Hi, In our porting of a big mysql app to postgres, we're finding lots of queries like select foo from (foo f, bar b) left join caz c on f.id = f.caz_id where f.id = b.foo_id I've seen the message where Tom explains why this is invalid in ANSI SQL so I converted it to select foo from foo f CROSS JOIN bar b left join caz c on f.id = f.caz_id where f.id = b.foo_id ...and it works. However, sometimes quite slowly. When we've looked into the slow ones, we've found that changing it again to select foo from foo f INNER JOIN bar b ON f.id = b.foo_id left join caz c on f.id = f.caz_id makes it perform much better. Furthermore, we're starting to find that performance of the 3rd is significantly better than the 2nd, *ONLY* when the CROSS JOINs are followed by more joins (like in this case). If there are no more tables being joined, changing to the 3rd version yields no performance gain. Are these three queries logically equivalent (well, at least the latter two since the first isn't valid SQL)? If so, does it make sense that the optimizer has difficulty with the second case. Cheers, Andy -- Andy Chambers -- 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 duplicate of existing operator
Hi, Is it possible to use CREATE OPERATOR to make behave like and? In general, for the built-in operators, is it possible to see their CREATE OPERATOR statements? Cheers, Andy -- Andy Chambers *Software Engineer * *MCNA Dental Plans* 200 West Cypress Creek Road Suite 500 Fort Lauderdale, FL 33309 954-730-7131 X186 (Office) 954-628-3347 (Fax) 1-800-494-6262 X141 (Toll Free) achamb...@mcna.net glip...@mcna.net (Email) www.mcna.net (Website) CONFIDENTIALITY NOTICE: This electronic mail may contain information that is privileged, confidential, and/or otherwise protected from disclosure to anyone other than its intended recipient(s). Any dissemination or use of this electronic mail or its contents by persons other than the intended recipient(s) is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail so that we may correct our internal records. Please then delete the original message. Thank you.
[GENERAL] Update takes longer than expected
Hi, I have an update that takes longer than expected and wondered if there's an easy way to make it go faster. It's pretty simple:- create table session ( id serial primary key, data text); update session set data = 'ipsum lorem...' where id = 5; The ipsum lorem.. stuff is an encrypted session variable from a rails app that does tend to get quite large select avg(length(data)) from session = 31275 We're trying to migrate the app from mysql to pg and this is one of the performance bottle-necks. Unfortunately it slows down every request by about 5 seconds. MySQL (both MyISAM and InnoDB) does this almost instantaneously. Cheers, 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] Update takes longer than expected
On Mon, Jan 9, 2012 at 4:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Sorry. Forgot to hit Reply to All Aha. I think I'd gotten carried away with some of the settings in order to optimize for bulk loading. Reverting back to the default postgresql.conf gets me back to the sort of times you guys are seeing here. Thanks, 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] immutable functions
The documentation has this to say about immutable functions... or otherwise use information not directly present in its argument list If the arguments are row variables, does this allow access to the data in the row? For example, is it safe to make the following function definition immutable. CREATE OR REPLACE FUNCTION distance(geocodes, geocodes) RETURNS double precision AS $BODY$ select case $1.zip = $2.zip when true then 0 else ((acos(sin(($1.lat) * (pi()/180)) * sin(($2.lat)*(pi()/180)) + cos(($1.lat)*(pi()/180)) * cos(($2.lat)*(pi()/180)) * cos(($1.lon - $2.lon) * (pi()/180*(180/pi())* 60 * 1.1515) end; $BODY$ LANGUAGE sql immutable COST 100; Cheers, 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] Batching up data into groups of n rows
Hi, I have a need to write a query that batches up rows of a table into groups of n records. I feel like this should be possible using the existing window functions but I can't figure out how to do it from the examples. So I have some table, let's say create table addresses ( line_1 text, line_2 text, city text, state text, zip text); ...and I want to select the data from that table, adding a group_id column, and a record_id column. The group_id would start at 1, and increment by 1 every 100 rows, and the record_id would increment by 1 every row, but restart at 1 for each new group_id. Thanks, 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] Batching up data into groups of n rows
On Mon, Sep 26, 2011 at 4:22 PM, Marti Raudsepp ma...@juffo.org wrote: On Mon, Sep 26, 2011 at 18:59, Andy Chambers achamb...@mcna.net wrote: ...and I want to select the data from that table, adding a group_id column, and a record_id column. The group_id would start at 1, and increment by 1 every 100 rows, and the record_id would increment by 1 every row, but restart at 1 for each new group_id. I can't see why you would want this, but regardless... The addresses need to be sent to a 3rd party web-service for canonicalization. The web service accepts batches of 100 addresses. I was wondering how I'd get Postgres to generate the XML for sending 100 addresses at a time to this web service. Since you didn't list a primary key for the table, I'm using ctid as a substitute. But that's a PostgreSQL-specific hack, normally you would use the real primary key instead. update addresses set group_id=(nr/100)+1, record_id=(nr%100)+1 from (select ctid, row_number() over () -1 as nr from addresses) as subq where subq.ctid=addresses.ctid; Cool! I don't need to actually store these ids in the database, they just need to be generated on the fly and forgotten but I think I can adapt the example to do what I need. Thanks, 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] Array syntax in the copier
Hi All, What's the correct method of writing array columns using the bulk copier. It seems to support the curly braces notation but how do you escape special characters like ,? I tried using \x2c and that prevents the syntax error but I end up with what looks like the string \x2c in the database. Thanks, Andy
[GENERAL] Cursors
Hi, What happens to cursors when new data is added to a table after you start iterating over its rows? For example, given the following loop... for rule in select tc.sid, tc.s, td.rule, td.returns from tcell tc inner join tcelldef td on (tc.p = td.p) where tc.iasid = current_audit_sid() or committed_sid in ( select committed from tcellread tcr where tc.sid = tcr.tcell ) for update of tc loop ... end loop; some code in the loop might add a record into tcellread that causes the where condition to become true for a row in which it was previously false. Will the cursor eventually see it? Thanks, 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] syntax for updating an aliased table
On Thu, May 26, 2011 at 1:40 PM, Rick Genter rick.gen...@gmail.com wrote: The UPDATE statement when multiple tables are involved always drives me nuts. I think what you need to do is remove all of the old. from the SET clause and use triple. in the WHERE clause instead of old. - and remove the old table alias from the UPDATE. This worked. Thanks very much -- 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] syntax for updating an aliased table
I'm confused about the correct syntax for updating an aliased table. I want to update triple from triple_updates where the data is different and tried to use the following update triple old set old.obln = new.obln, old.ointv = new.ointv, old.otime = new.otime, old.oflt = new.oflt, old.ostr = new.ostr, old.oint = new.oint, old.oda = new.oda, old.uasid = new.uasid from triple_update as new where (old.s = new.s and old.g = new.g) and ( old.obln new.obln or old.ointv new.ointv or old.otime new.otime or old.oflt new.oflt or old.ostr new.ostr or old.oint new.oint or old.oda new.oda or old.uasid new.uasid) ...but postgres complains about not having column old in the triple table. Putting an as between triple and old on the first line didn't make any difference. If I leave out the old alias, it complains about the columns being ambiguous. How should the query above be changed to be syntactically correct? Thanks, Andy
[GENERAL] Cursor metadata
Hi All, Is there anywhere in the postgres catalog where one can access metadata about a held cursor. Type information in particular would be really useful. Cheers, Andy -- Andy Chambers -- 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] Howto use COPY FROM with the native API?
On Mon, 11 Apr 2011 11:21:16 -0400, Clemens Eisserer linuxhi...@gmail.com wrote: Hi, We are working on an university project for network traffic accounting. We use ulogd-2 to log netfilter packets, however it creates for each packet a seperate transaction and also doesn't use prepared statements, which results in horrible performance. What we are looking for is a low-overhead way of inserting many rows (100-1000) into a table at once in one transaction. Is COPY FROM STDIN a good idea in this case? For that many, you can probably get away with insert into foo values (1, 2, 3), (4, 5, 6) ..rather than having to figure out the COPY FROM API. Cheers, 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] Dynamic Assignment
Hi All, In a trigger function, I'm trying to set the variable pkey to be one of the columns in the automatic variable NEW. Which one depends on some metadata that is available at run-time. I'm having a hard time using an automatic variable in a dynamic execute command. I get the error missing FROM-clause entry for table new Here's my function create or replace function refresh_row () returns trigger as $$ declare pkey bigint; begin execute 'select NEW.esid' into pkey; end; $$ language plpgsql Obviously this particular code could be re-written as a simple assignment but I need the esid part to be dynamic. Is this possible? Cheers, Andy -- Andy Chambers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trigger Function return values
Hi, How is the return value of a trigger function defined in plpgsql used? I can't find anything in the documentation but some of the examples return NULL, and others return something like NEW. It seems that if the return statement is omitted, an error occurs when the trigger is fired. -- Andy Chambers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Enable/Disable Triggers
Hi All, Is it a big no-no to enable/disable triggers by manually setting pg_trigger.tgenabled to 'D'? -- Andy Chambers -- 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] Schema version control
On Thu, 10 Feb 2011 17:59:30 -0500, Bill Moran wmo...@potentialtech.com wrote: The overview: You store your schema and data as XML (this is easy to migrate to, because it includes a tool that makes the XML from a live database) We're doing a similar thing here except we're a Lisp shop so our schema is defined as a set of defentities and we can migrate from one version to another using a corresponding set of defmaps. Keep your XML schema files in some RCS. When it's time for a new deployment, you run the dbsteward tool against the schema XML and it turns it into DDL and DML. When it's time for an upgrade, you run the dbsteward tool against two schema XML files, and it calculates what has changed and generates the appropriate DDL and DML to upgrade. This sounds pretty cool. Ours doesn't do that yet but that's next on my TODO list. -- Andy Chambers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Table inheritance foreign key problem
Hi,
One of the caveats described in the documentation for table inheritance is
that foreign key constraints cannot cover the case where you want to check
that a value is found somewhere in a table or in that table's
descendants. It says there is no good workaround for this.
What about using check constraints?
So say you've got cities and capitals from the example and you had some
other table that wanted to put a foreign key on cities (plus capitals).
For example, lets keep guidebook info for the cities. Some cities are
worthy of guidebooks even though they're not capitals. Rather than put a
foreign key constraint on city, would the following work? What are the
drawbacks?
create table guidebooks (
city check (city in (select name
from cities)),
isbn text,
author text,
publisher text);
insert into guidebooks ('Barcelona', ) -- not a capital
insert into guidebooks ('Edinburgh', ) -- a capital
insert into guidebooks ('France', ) -- fail
--
Andy Chambers
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
