Re: [GENERAL] use null or 0 in foreign key column, to mean "no value"?
> On 27 Jun 2015, at 5:59, Robert Nikander wrote:
> In application code, prepared statements want to say: `select * from items
> where color_id = ?` and that `?` might be a int or null, so that doesn’t
> work.
You could add another parameter to test which expression to 'activate' in the
query, something like:
select * from items where ('notnull' = ? and color_id = ?) or ('null' = ? and
color_id is null);
Of course, with those _positional_ query parameters that means you need to add
the same value TWICE into the query. You wouldn't need to with _named_ query
parameters, if those are available to you.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] HA
hi, peoples! Have a way to distribute connections for postgres master x master? I need something... When i connect my apps, direct thia connections only one IP, but this ip, automaticaly resolve one of my two masters servers Example: app <---> ip 10.1.1.1 This 10.1.1.1 see my two masters in 10.1.1.2 and 10.1.1.3, control and direct my connections... For the master x master, i used Xdb multimaster replication -- T.'.A.'.F.'., *Gerdan Rezende dos Santos * *Especialista PostgreSQL & EnterpriseDB* ger...@gmail.com +55 (61) 9645-1525 www.tecnisys.com.br
Re: [GENERAL] HA
Give a look to PEN, it is a load-balancing daemon :) Luis Daniel Lucio Quiroz CISSP, CISM, CISA Linux, VoIP and much more fun www.okay.com.mx Need LCR? Check out LCR for FusionPBX with FreeSWITCH Need Billing? Check out Billing for FusionPBX with FreeSWITCH 2015-06-27 9:32 GMT-04:00 Gerdan Rezende dos Santos : > hi, peoples! > Have a way to distribute connections for postgres master x master? > I need something... When i connect my apps, direct thia connections only > one IP, but this ip, automaticaly resolve one of my two masters servers > > > Example: app <---> ip 10.1.1.1 > This 10.1.1.1 see my two masters in 10.1.1.2 and 10.1.1.3, control and > direct my connections... > > > For the master x master, i used Xdb multimaster replication > > > -- > T.'.A.'.F.'., > > *Gerdan Rezende dos Santos * > *Especialista PostgreSQL & EnterpriseDB* > ger...@gmail.com > +55 (61) 9645-1525 > www.tecnisys.com.br > >
Re: [GENERAL] Inserting from multiple processes?
Hi Dave:
On Fri, Jun 26, 2015 at 2:59 AM, Dave Johansen wrote:
> It appears that calling "SELECT insert_test_no_dup('2015-01-01', 1, 1)"
> cause the XID to increment? I'm not sure if it's only when the exception
> happens or all the time, but if there some way to prevent the increment of
> XID because it's causing problems with our system:
> http://www.postgresql.org/message-id/CAAcYxUer3MA=enxvnowe0osaa8comvxcf6orhp-vuppr56t...@mail.gmail.com
I, personally, would expect an START TRANSACTION to burn an XID, they
are serial, and they need to be allocated to have transaction
ordering, like the thing which happens with the sequences. I assume
the server can have some optimizations ( like delaying XID adquisition
to the first appropiate statement, which I think depends on your
isolation level ), but I would never expect it to not allocate it
before an insert, it needs it to be sent to the table, in case it
succeeds, and has to acquire it beforehand, in case someone needs to
acquire another xid between the time it starts inserting and the time
it succeeds or fail. Some internals expert may shed some light, but
after reading your link it seems your problem is just you do too many
transactions without a vacuum ( also reading your pointed threas it
sees you do vacuum fulls, which seems unneeded ) and expecting
postgres has some kind of magic to avoid burning the xids.
Francisco Olarte.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] varchar sort ordering ignore blanks - SOLVED!
To make a long story short, few years ago I was complaining that the sort order of varchar, in UTF-8, was ignoring blanks, that is that the sort order was: a a A A à b B But, i was in need of a sort order that would place blanks BEFORE the other char, that is I would like to have the following sort order: a A a A à b B Now, using Debian Jessie, I found the solution to this issue. I needed to change the file /usr/share/i18n/locales/iso14651_t1_common placing in the proper position the sort order. This is the output of the diff between the file before and after the modification: diff iso14651_t1_common.orig iso14651_t1_common 4837d4836 < IGNORE;IGNORE;IGNORE; # 32 5080a5080 > ;;IGNORE # 32 Beware: the change is sistem wide, and regards all character sets and locales... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Weird insert issue
I'm running this SQL statement: insert into Favorite (patronId, titleId) select 123, 234 where not exists ( select 1 from Favorite where patronId = 123 and titleId = 234 ) It normally runs perfectly, but will rarely fail and I just can't see any way that it could. :-| The exception I get is that the unique key (patronid+titleid) was violated. Is it possible that the statement is getting run twice and that the timing is such that the first one succeeds and the second tries to do the insert and fails because the select part of the SQL ran before the first insert completed? I'd expected that each of the two would be single operations, but this error is making me rethink that. Any thoughts? Larry -- 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 insert issue
2015-06-28 6:37 GMT+02:00 Larry Meadors : > I'm running this SQL statement: > > insert into Favorite (patronId, titleId) > select 123, 234 > where not exists ( > select 1 from Favorite where patronId = 123 and titleId = 234 > ) > > It normally runs perfectly, but will rarely fail and I just can't see > any way that it could. :-| > > The exception I get is that the unique key (patronid+titleid) was violated. > > Is it possible that the statement is getting run twice and that the > timing is such that the first one succeeds and the second tries to do > the insert and fails because the select part of the SQL ran before the > first insert completed? I'd expected that each of the two would be > single operations, but this error is making me rethink that. > sure - it is expected behave http://www.postgresql.org/docs/9.4/static/transaction-iso.html you can protect it against this issue with locking - in this case you can try "for update" clause http://www.postgresql.org/docs/9.4/static/explicit-locking.html insert into Favorite (patronId, titleId) select 123, 234 where not exists ( select 1 from Favorite where patronId = 123 and titleId = 234 for update ) Regards Pavel > > Any thoughts? > > Larry > > > -- > 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 insert issue
On Sat, Jun 27, 2015 at 9:47 PM, Pavel Stehule wrote: > you can protect it against this issue with locking - in this case you can > try "for update" clause > > http://www.postgresql.org/docs/9.4/static/explicit-locking.html > > insert into Favorite (patronId, titleId) > select 123, 234 > where not exists ( > select 1 from Favorite where patronId = 123 and titleId = 234 for update > ) That won't work reliably either -- a SELECT ... FOR UPDATE will still use an MVCC snapshot. The looping + subxact pattern must be used [1] if a duplicate violation isn't acceptable. ON CONFLICT DO UPDATE should be preferred once 9.5 is released. [1] http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE -- Regards, Peter Geoghegan -- 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 insert issue
2015-06-28 6:52 GMT+02:00 Peter Geoghegan : > On Sat, Jun 27, 2015 at 9:47 PM, Pavel Stehule > wrote: > > you can protect it against this issue with locking - in this case you can > > try "for update" clause > > > > http://www.postgresql.org/docs/9.4/static/explicit-locking.html > > > > insert into Favorite (patronId, titleId) > > select 123, 234 > > where not exists ( > > select 1 from Favorite where patronId = 123 and titleId = 234 for > update > > ) > > That won't work reliably either -- a SELECT ... FOR UPDATE will still > use an MVCC snapshot. The looping + subxact pattern must be used [1] > if a duplicate violation isn't acceptable. ON CONFLICT DO UPDATE > should be preferred once 9.5 is released. > > [1] > http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE > yes, you have true - cannot to lock, what doesn't exists in pg Regards Pavel > -- > Regards, > Peter Geoghegan >
