Re: [GENERAL] Prepared statement already exists
On Mon, Dec 8, 2008 at 09:17, Tomasz Ostrowski <[EMAIL PROTECTED]> wrote:
> So:
>
> sql_md5 = md5(sql);
> try {
>PREPARE sql_md5 AS sql;
> } catch (SQLException e) {
>if (! e.getSQLState().equals("42P05")) {
>throw e;
>}
> }
> EXECUTE sql_md5;
Yeah, well, like I said, I have to write it in PHP and apparently
"old" or should I say "classic" extensions like Postgres don't trigger
specific but generic exceptions (ie. the catch block above, while
possible, is useless). There are even cases where PHP doesn't throw
catchable exceptions but an "older" kind; which can be handled
globally with a custom exception handler, but doing this for an entire
application just for the sake of one part of it is more trouble than
it's worth (in this case).
In case anybody is interested, here is the way I went. I created a
singleton class which contains a static hashtable. Every time a new
instance is requested it queries the pg_prepared_statements table and
adds values from the column "name" to the hashtable. This way the main
method can avoid clashes fairly well.
This mechanism is still not perfect. Technically it is still possible
for race conditions to appear. Apparently (in PHP at least) pg_connect
does persistent connections by default. If this is overlooked it is
possible for two web pages to use the same connection and one of them
to define a statement a short while before another, which would cause
a clash. Because while connections may be shared, the code-side
hashtable is not. Solution: either make sure connections are NOT
shared, or implement a way to properly share the hashtable across
pages. Or implement the whole thing in Postgres, transparently.
--
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] Prepared statement already exists
On 2008-11-20 12:56, WireSpot wrote:
On Thu, Nov 20, 2008 at 10:56, Albe Laurenz <[EMAIL PROTECTED]> wrote:
Do you still need the old prepared statement?
If not, you can simple DEALLOCATE it and then try the PREPARE again.
Yes, I'd like to keep the old statements, that's part of the perks --
if a query will be repeated it will (possibly) benefit from the
statement being already prepared.
So:
sql_md5 = md5(sql);
try {
PREPARE sql_md5 AS sql;
} catch (SQLException e) {
if (! e.getSQLState().equals("42P05")) {
throw e;
}
}
EXECUTE sql_md5;
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh
--
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] Monty on MySQL 5.1: "Oops, we did it again"
As I read it, he is supportive of the community process that PG follows; I am not so sure he promotes Postgres though :) On Thu, Dec 4, 2008 at 3:56 AM, Bruce Momjian <[EMAIL PROTECTED]> wrote: > Jason Long wrote: > > Greg Smith wrote: > > > I wonder if I'm the only one who just saved a copy of that post for > > > reference in case it gets forcibly removed... > > > > > > Recently I was thinking about whether I had enough material to warrant > > > a 2008 update to "Why PostgreSQL instead of MySQL"; who would have > > > guessed that Monty would do most of the research I was considering for > > > me? > > > > > > -- > > > * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, > MD > > > > > I quit using MySQL years ago when the default table type did not have > > transactions and subqueries were not existent. The features I was > > looking for were already in PostgreSQL for several versions. > > > > I am surprised to see such an honest post regarding MySQL. > > Monty is quite supportive of Postgres. > > -- > Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us > EnterpriseDB http://enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
Re: [GENERAL] Unique constaint violated without being violated
funny!! :) too bad, i also noticed it only after reading Merlin's response :) On Sat, Dec 6, 2008 at 11:12 AM, Sebastian Tennant <[EMAIL PROTECTED]>wrote: > Quoth "Merlin Moncure" <[EMAIL PROTECTED]>: > > It looks to me like you are setting the whole table to the same > > address in the update statement (no where clause)...so of course you'd > > get the error. Maybe you want to do an insert statement? > > > > merlin > > Doh! Thanks Merlin. I'm so glad it's just my mind that's going! :-) > > Sebastian > > > -- > 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] Planner picking topsey turvey plan?
Glyn Astill <[EMAIL PROTECTED]> writes: > Does anyone know how I can change what I'm doing to get pgsql to pick a > better plan? You've provided no evidence that this is a bad plan. In particular, the plan you seem to think would be better would involve an estimated 153 iterations of the cost-15071 hash aggregation, which simple arithmetic shows is more expensive than the plan it did choose. 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] COPY error with null date
Bill Todd <[EMAIL PROTECTED]> writes: > I am beginning to suspect this is impossible. That's correct: see the COPY reference page. A quoted value is never considered to match the NULL string. 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] COPY error with null date
Joshua D. Drake wrote: On Fri, 2008-12-05 at 12:00 -0700, Bill Todd wrote: Joshua D. Drake wrote: On Thu, 2008-12-04 at 19:35 -0700, Bill Todd wrote: null as IS NULL results in the following error. ERROR: syntax error at or near "is" LINE 5: null as is null ^ ** Error ** ERROR: syntax error at or near "is" SQL state: 42601 Character: 109 Any other suggestions? COPY foo FROM '/tmp/bar' NULL as 'NULL' copy billing.contact from 'c:/export/contact.csv' with csv delimiter as ',' null as 'NULL' quote as '"'; produces the same error. I am beginning to suspect this is impossible. :-(
Re: [GENERAL] ALTER TABLE .....Error: Must be owner of the table
"Josh Harrison" <[EMAIL PROTECTED]> writes: > How can I give the ALTER permission You can't grant ALTER permission --- that's only allowed to the table owner. However, you could make thw table be owned by a group role and grant membership in that role. 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] tuples
> I have a question concerning psql. I found that psql has a defined > command '-t' and that it turns off printing of column names > and result > row count footers, etc. > > what I look for, is a command, which would turn off result row count > footer, but would print column names. > > is there an easy way to do this? Start psql with the -P footer switch. 8.3.3 works this way. Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] shared disk failover
Hi, any one has doing this... is there a good tutorial o directions for it? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] domain+enum
One thing I don't understand, can someone please explain that to me: (that's on 8.4, but it "works" same way on 8.3) CREATE TYPE rcount AS ENUM ( 'one', 'two', 'three' ); CREATE DOMAIN foocount AS rcount DEFAULT 'one' NOT NULL; CREATE DOMAIN foostamp AS bigint NOT NULL DEFAULT (EXTRACT(epoch FROM timeofday()::timestamp)*100)::bigint CHECK (VALUE > 0); now: gj=# create table footest(a int not null, b rcount default 'one' not null); CREATE TABLE gj=# insert into footest(a) select generate_series(1,100); INSERT 0 100 gj=# update footest set b = 'three' where random() < 0.5; UPDATE 37 gj=# update footest set b = 'two' where random() > 0.5; UPDATE 41 gj=# select count(*) from footest where b = 'three'; count --- 23 (1 row) Works perfectly well, with enums. Now, trying to do the same thing, with enum in domain: gj=# create table footest(a int not null, b foocount, c foostamp); CREATE TABLE gj=# insert into footest(a) select generate_series(1,100); INSERT 0 100 gj=# update footest set b = 'two' where random() > 0.5; UPDATE 45 gj=# update footest set b = 'three' where random() < 0.5; UPDATE 47 gj=# select count(*) from footest where b = 'three'; ERROR: operator does not exist: foocount = unknown LINE 1: select count(*) from footest where b = 'three'; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. Now, I could understand that - if that was the problem with domains in general, but : gj=# select count(*) from footest where c < 1234; count --- 0 (1 row) Please, can someone explain that strange behavior to me ? I do consider it a buggy one, to be honest. -- 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] ERROR: compressed data is corrupt, ignore earlier message
Oops. I switched over to a test machine (laptop) that hadn't been used in a while. Didn't think to check pg version before loading up test db. I'm running 8.3.1. S... Never mind. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.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
[GENERAL] ERROR: compressed data is corrupt
I'm seeing that now when trying to run an update. Is there anything I should investigate? This is a test database, and I can just wipe it and restore from dump. BTW, this is not related to my earlier message about rebuilding a RAID ;-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.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] RAM is good!
On Sun, Dec 7, 2008 at 10:00 AM, Scott Ribe <[EMAIL PROTECTED]> wrote: > I know that my database is not that big (4-5GB), but I am shocked at how > well it's performing during a RAID rebuild. For light use, the difference is > hardly noticeable. Nothing like avoiding hitting the disk ;-) Our db size is around 25G and we have 32 Gig of ram. We've had one drive in our RAID-10 fail and didn't even notice it performance wise. I'll never use RAID5 on a heavy working production db, as rebuilds and degraded performance are just horrible. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] RAM is good!
I know that my database is not that big (4-5GB), but I am shocked at how well it's performing during a RAID rebuild. For light use, the difference is hardly noticeable. Nothing like avoiding hitting the disk ;-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.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
