[SQL] Escaping \
Hello all, I've a problem with escaping a \ in a string. When I enter the query: SELECT '''\\\''; I get the right result: '\' But when I try this in a Function: CREATE FUNCTION sp_tmp() RETURNS varchar(10) AS ' SELECT ''\\\' AS RESULT' LANGUAGE 'sql'; I get the following Parse-erros: psql:tmp1:4: ERROR: Unterminated quoted string. I don't understand why, when you leave the SELECT-statement out you get '''\\\'' and that is not unterminated. Please help Greetings Martijn van Dijk ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: Calling Java from psql (was Re: [SQL] requesting help)
At 10:33 26/03/01 +0200, Mathijs Brands wrote: >Has anybody ever tried calling Java code from a pgsql trigger written >in C? Shouldn't this be possible using JNI? This was discussed recently. >I'm not exactly a Java expert myself, but this is the way PHP allows >you to call Java code from your PHP scripts. It may be something to look at when things quieten down a bit. I'm rushed of my feet at the moment (busy at work, long hours etc) but once 7.1 is finally out of the door, I may get the chance to start looking at JNI (& my employers don't mind me spending some time working on postgres when I'm not on other projects either which is nice ;-) ) PS: the JDBC list is the best place for java based emails - I rarely read sql ;-) Peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Self-Referencing
On Thursday, 29. March 2001 01:38, David Olbersen wrote: [snip] > SELECT > building_id, > num_buildings, > ( > SELECT count( building_id ) > FROM building_portals > WHERE building_id = THIS.building_id > ) > FROM buildings; Try this query (untested), using table aliases: SELECT b.building_id, b.num_buildings, ( SELECT count( bp.building_id ) FROM building_portals bp WHERE bp.building_id = b.building_id ) FROM buildings b; You could also write . instead. Hope that helps. Christof -- gl.aser . software engineering . internet service http://gl.aser.de/ . Planckstraße 7 . D-39104 Magdeburg Tel. +49.391.7 44 77 10 . Fax +49.391.7 44 77 13 . Mobil 0177.77 92 84 3 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Calling Java from psql (was Re: [SQL] requesting help)
At 21:37 26/03/01 +0200, Mathijs Brands wrote: >On Mon, Mar 26, 2001 at 07:00:43PM +0200, Peter Eisentraut allegedly wrote: > > Mathijs Brands writes: > > > > > Has anybody ever tried calling Java code from a pgsql trigger written > > > in C? Shouldn't this be possible using JNI? > > > > I have, and given the current Java implementations it's a desaster. > >That bad eh? Well, I must admit I couldn't get the PHP-Java coupling to >work stable either :( Not having looked at the PHP-Java link, are they working as one process or is it some IPC type link? PeterM ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] SELECT ... FOR UPDATE
On Wed, 28 Mar 2001, Marcos Minshew wrote: > I am interested in using the SELECT ... FOR UPDATE feature but it doesn't > work quite the way I had hoped. If there is a better/different way of doing > this please enlighten me. > > If I issue: > > BEGIN; > SELECT * FROM atable WHERE atable.key = 10 FOR UPDDATE; > > in one session and then issue the same commands from a 2nd session, the 2nd > session simply waits until the 1st session issues COMMIT or ROLLBACK. While > the 2nd session is waiting I am, apparently locked out. What I would like > is for the 2nd session to determine if the lock can be obtained and if not, > offer the user a choice of waiting or escaping from the SELECT. Is this > possible? > > Using 7.0.3 (I actually almost wrote an email asking this same question) Has the documentation been updated recently or something I can find it elsewhere (ie, not pg manual)? I think (maybe) I am not the only person confused by documentation for LOCK and MVCC. "lack of detail / examples" would probably say it best... Thanks, -Cedar ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Self-Referencing
On Wed, 28 Mar 2001, David Olbersen wrote: > Hello, > > I have a feeling this isn't going to make much sense, but I'm gonig to try > anyway. > > What I'd like to do is be able to refer to an outer-SELECT from an > inner-SELECT. I hope this makes sense. > > I need to be able to refer to the row that's being processed in a SELECT. I'm > going to use the idea of 'this' referring to the row that's currently being > processed. Here's the example of what I'd like: > > SELECT > building_id, > num_buildings, > ( > SELECT count( building_id ) > FROM building_portals > WHERE building_id = THIS.building_id > ) > FROM buildings; > > Am I making things too complicated, and if so will somebody *PLEASE* tell me > the easier way to do this. Thanks. > > -- Dave If I understand what you're trying to do, try this: SELECT building_id, num_buildings, ( SELECT count( building_id ) FROM building_portals WHERE building_id = THIS.building_id ) FROM buildings AS THIS; __^^^ You might also want to add in a 'AS num_portals' after the sub-select. -Cedar ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Function with now() | time 'now' | etc...
> [EMAIL PROTECTED] writes: > > It doesn't getting different times on each execution. I also tried put > > "timestamp 'now'" insted "now()". What am I doing wrong? > > now() is defined to return the time of the start of the current > transaction. It won't change value inside a transaction. See > >http://www.postgresql.org/devel-corner/docs/postgres/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT What about CURRENT_TIMESTAMP? (This is standard SQL isn't it?) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Escaping \
From: "Martijn van Dijk" <[EMAIL PROTECTED]> > I've a problem with escaping a \ in a string. > > When I enter the query: > > SELECT '''\\\''; I get the right result: '\' > > But when I try this in a Function: > > CREATE FUNCTION sp_tmp() RETURNS varchar(10) > AS ' > SELECT ''\\\' AS RESULT' > LANGUAGE 'sql'; > > I get the following Parse-erros: > > psql:tmp1:4: ERROR: Unterminated quoted string. I don't understand why, > when you leave the SELECT-statement out you get '''\\\'' and that is not > unterminated. Had something similar myself the other day. The reason is that you are already one deep in Postgres' string-parser, so you need something like: SELECT '''' AS RESULT HTH - Richard Huxton ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Calling Java from psql (was Re: [SQL] requesting help)
On Thu, Mar 29, 2001 at 10:10:44AM +0100, Peter Mount allegedly wrote: > At 21:37 26/03/01 +0200, Mathijs Brands wrote: > >On Mon, Mar 26, 2001 at 07:00:43PM +0200, Peter Eisentraut allegedly wrote: > > > Mathijs Brands writes: > > > > > > > Has anybody ever tried calling Java code from a pgsql trigger written > > > > in C? Shouldn't this be possible using JNI? > > > > > > I have, and given the current Java implementations it's a desaster. > > > >That bad eh? Well, I must admit I couldn't get the PHP-Java coupling to > >work stable either :( > > Not having looked at the PHP-Java link, are they working as one process or > is it some IPC type link? They run a seperate JVM and connect to it. At least that what I gathered. Regards, Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] serial type; race conditions
How does currval work if you are not inside a transaction. I have been experimenting with inserting into a table that has a sequence. If the insert fails (not using a transaction) because of bad client input then the next insert gets the proper next number in the sequence. given sequence 1,2,3,4,5 exists insert into table date 1/111/01 (obviously wrong) insert fails... try again with good data, insert succeeds and gets number 6 in the sequence. i'm getting what I want. A sequence number that does not increment on a failed insert. However, how do I get the assigned sequence number with currval when I am not using a transaction? What happens when multiple users are inserting at the same time? I am trying to create a sequence with out any "missing" numbers. If there is a failure to insert, and a sequence number is "taken". I want the empty row. Thanks, it is getting clearer Ted -Original Message- From: Bruce Momjian <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Date: Mon, 26 Mar 2001 16:47:37 -0500 (EST) Subject: Re: [SQL] serial type; race conditions > > Hi, > > > > I'm using serial fields to generate IDs for almost all object in my > > database. I insert an empty row, get the CURRVAL() of the sequence > > and then update to that value. > > > > I had understood (and now, I can't find the reference to back this > up) > > that serial is implemented in such a way that race conditions between > > DB connections can't happen. > > > > Is this true? > > Safe. See FAQ item. currval is for your backend only. > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania > 19026 > > ---(end of > broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] serial type; race conditions
> How does currval work if you are not inside a transaction. I have > been experimenting with inserting into a table that has a sequence. > If the insert fails (not using a transaction) because of bad client input > then the next insert gets the proper next number in the sequence. If you are in a transaction, and the INSERT succeeds but the transaction rolls back, the sequence does not get reused. Each backend has a local variable that holds the most recent sequence assigned. That is how currval works. > > given sequence 1,2,3,4,5 exists > insert into table date 1/111/01 (obviously wrong) insert fails... > try again with good data, insert succeeds and gets number 6 in the > sequence. > > i'm getting what I want. A sequence number that does not increment > on a failed insert. However, how do I get the assigned sequence > number with currval when I am not using a transaction? What > happens when multiple users are inserting at the same time? > > I am trying to create a sequence with out any "missing" numbers. If > there is a failure to insert, and a sequence number is "taken". I want > the empty row. > > Thanks, it is getting clearer You really can't use sequences with no gaps. Sequence numbers are not _held_ until commit because it would block other backends trying to get sequence numbers. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] serial type; race conditions
I ditto what Bruce said - trying to get a true sequence without gaps is a
losing battle. Why don't you, instead, use a serial column as the real
sequence, and then a trigger that simply inserts max(foo) + 1 in a
different column? Then when you need to know the column, do something
like:
SELECT number_i_care_about FROM table WHERE serial_number =
currval('serial_number_seq');
ap
--
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
(Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
[EMAIL PROTECTED] - http://www.unc.edu/~aperrin
On Thu, 29 Mar 2001, Bruce Momjian wrote:
> > How does currval work if you are not inside a transaction. I have
> > been experimenting with inserting into a table that has a sequence.
> > If the insert fails (not using a transaction) because of bad client input
> > then the next insert gets the proper next number in the sequence.
>
> If you are in a transaction, and the INSERT succeeds but the transaction
> rolls back, the sequence does not get reused. Each backend has a local
> variable that holds the most recent sequence assigned. That is how
> currval works.
>
> >
> > given sequence 1,2,3,4,5 exists
> > insert into table date 1/111/01 (obviously wrong) insert fails...
> > try again with good data, insert succeeds and gets number 6 in the
> > sequence.
> >
> > i'm getting what I want. A sequence number that does not increment
> > on a failed insert. However, how do I get the assigned sequence
> > number with currval when I am not using a transaction? What
> > happens when multiple users are inserting at the same time?
> >
> > I am trying to create a sequence with out any "missing" numbers. If
> > there is a failure to insert, and a sequence number is "taken". I want
> > the empty row.
> >
> > Thanks, it is getting clearer
>
> You really can't use sequences with no gaps. Sequence numbers are not
> _held_ until commit because it would block other backends trying to get
> sequence numbers.
>
> --
> Bruce Momjian| http://candle.pha.pa.us
> [EMAIL PROTECTED] | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] serial type; race conditions
If you are looking to have every number accounted for, something like this
will work:
INSERT INTO table (serial_col) SELECT nextval('seq_serial_col');
UPDATE table SET foo = 'bar' , ... WHERE serial_col = (SELECT
currval('seq_serial_col'));
then, if the update fails, the number will be accounted for in the
table (Note that you could not use not null on any of the columns).
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Thu, 29 Mar 2001, postgresql wrote:
> How does currval work if you are not inside a transaction. I have
> been experimenting with inserting into a table that has a sequence.
> If the insert fails (not using a transaction) because of bad client input
> then the next insert gets the proper next number in the sequence.
>
> given sequence 1,2,3,4,5 exists
> insert into table date 1/111/01 (obviously wrong) insert fails...
> try again with good data, insert succeeds and gets number 6 in the
> sequence.
>
> i'm getting what I want. A sequence number that does not increment
> on a failed insert. However, how do I get the assigned sequence
> number with currval when I am not using a transaction? What
> happens when multiple users are inserting at the same time?
>
> I am trying to create a sequence with out any "missing" numbers. If
> there is a failure to insert, and a sequence number is "taken". I want
> the empty row.
>
> Thanks, it is getting clearer
>
> Ted
>
>
> -Original Message-
> From: Bruce Momjian <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Date: Mon, 26 Mar 2001 16:47:37 -0500 (EST)
> Subject: Re: [SQL] serial type; race conditions
>
> > > Hi,
> > >
> > > I'm using serial fields to generate IDs for almost all object in my
> > > database. I insert an empty row, get the CURRVAL() of the
> sequence
> > > and then update to that value.
> > >
> > > I had understood (and now, I can't find the reference to back this
> > up)
> > > that serial is implemented in such a way that race conditions
> between
> > > DB connections can't happen.
> > >
> > > Is this true?
> >
> > Safe. See FAQ item. currval is for your backend only.
> >
> > --
> > Bruce Momjian| http://candle.pha.pa.us
> > [EMAIL PROTECTED] | (610) 853-3000
> > + If your life is a hard drive, | 830 Blythe Avenue
> > + Christ can be your backup.| Drexel Hill, Pennsylvania
> > 19026
> >
> > ---(end of
> > broadcast)---
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to
> > [EMAIL PROTECTED])
>
>
>
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: Calling Java from psql (was Re: [SQL] requesting help)
Peter Mount wrote:
>
> At 10:33 26/03/01 +0200, Mathijs Brands wrote:
>
> >Has anybody ever tried calling Java code from a pgsql trigger written
> >in C? Shouldn't this be possible using JNI?
>
> This was discussed recently.
>
> >I'm not exactly a Java expert myself, but this is the way PHP allows
> >you to call Java code from your PHP scripts.
>
> It may be something to look at when things quieten down a bit. I'm rushed
> of my feet at the moment (busy at work, long hours etc) but once 7.1 is
> finally out of the door, I may get the chance to start looking at JNI (& my
> employers don't mind me spending some time working on postgres when I'm not
> on other projects either which is nice ;-) )
>
> PS: the JDBC list is the best place for java based emails - I rarely read
> sql ;-)
>
> Peter
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
another possible angle is to use perl and its new inline module
{not sure if java is fully supported yet...}
or its other perl->java binding
it should be avail a t cpan
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Escaping \
"Martijn van Dijk" <[EMAIL PROTECTED]> writes: > But when I try this in a Function: > CREATE FUNCTION sp_tmp() RETURNS varchar(10) > AS ' > SELECT ''\\\' AS RESULT' > LANGUAGE 'sql'; > I get the following Parse-erros: You need an extra level of quoting because the function body is itself a string literal. You might find the quoting discussion in http://www.postgresql.org/devel-corner/docs/postgres/plpgsql-porting.html helpful. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Calling Java from psql (was Re: [SQL] requesting help)
A James Lewis writes: > Before I go investigating this, is it possible to trigger an arbitrary > program from the SQL, say a shell script? At the lowest level, you can generally do anything a C program can do. Writing the equivalent of system() in SQL should be rather trivial. You can also take a look at my little toy project PL/sh: http://www.postgresql.org/~petere/plsh.html -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Calling Java from psql (was Re: [SQL] requesting help)
Peter Eisentraut <[EMAIL PROTECTED]> writes: > A James Lewis writes: >> Before I go investigating this, is it possible to trigger an arbitrary >> program from the SQL, say a shell script? > At the lowest level, you can generally do anything a C program can do. > Writing the equivalent of system() in SQL should be rather trivial. I'm generally pretty suspicious of any system design that requires calling outside programs from an SQL function. The problem is that this fundamentally breaks transactional semantics: if the transaction is rolled back after the function call, its effects inside the database disappear ... but there's no way to roll back whatever the outside program did. Now you have a consistency problem. I'd suggest thinking hard about how to restructure your system design so that you do not need this capability. Yes, it'd be easy to implement, but that doesn't make it a good idea. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Update taking forever
I am trying to do an update on column in a table with 1.5 millions rows. The SQL is as follows, I am also putting it in a transaction in case things go wrong. begin; update statistics set parameters = NULL where parameters =''; An explain produces the following: - Seq Scan on statistics (cost=0.00..56174.49 rows=14976 width=88) Would anyone be able to tell me why it is seemingly infinite, i'm running linux, postgres v7.0 Cheers Graham winmail.dat ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] RE: serial type; race conditions
Probably just me: but I don't see the point. Consider:
* User 1 commences insert transaction: grabs nextval(sequence),
max(foo)
* User 2 commences insert transaction: grabs nextval(sequence),
max(foo)
* User 1 commits
* User 2 commits (insert has sequence value one higher than for User
1, but same value for max(foo) + 1), or
* If foo has a unique constraint, transaction 2 will roll back.
Either way, I don't see what has been gained. All of the messages I have
read on this subject conclude with the same point: choice is to:
* accept unique sequence with holes
* accept loss of concurrency (as in the example above).
Or am I just missing the point?
> -Original Message-
> From: Andrew Perrin [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, March 29, 2001 8:46 AM
> To: [EMAIL PROTECTED]
> Cc: PgSQL-SQL
> Subject: Re: serial type; race conditions
>
> I ditto what Bruce said - trying to get a true sequence without gaps is a
> losing battle. Why don't you, instead, use a serial column as the real
> sequence, and then a trigger that simply inserts max(foo) + 1 in a
> different column? Then when you need to know the column, do something
> like:
>
> SELECT number_i_care_about FROM table WHERE serial_number =
> currval('serial_number_seq');
>
> ap
>
> --
> Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
> (Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
> [EMAIL PROTECTED] - http://www.unc.edu/~aperrin
>
> On Thu, 29 Mar 2001, Bruce Momjian wrote:
>
> > > How does currval work if you are not inside a transaction. I have
> > > been experimenting with inserting into a table that has a sequence.
> > > If the insert fails (not using a transaction) because of bad client
> input
> > > then the next insert gets the proper next number in the sequence.
> >
> > If you are in a transaction, and the INSERT succeeds but the transaction
> > rolls back, the sequence does not get reused. Each backend has a local
> > variable that holds the most recent sequence assigned. That is how
> > currval works.
> >
> > >
> > > given sequence 1,2,3,4,5 exists
> > > insert into table date 1/111/01 (obviously wrong) insert fails...
> > > try again with good data, insert succeeds and gets number 6 in the
> > > sequence.
> > >
> > > i'm getting what I want. A sequence number that does not increment
> > > on a failed insert. However, how do I get the assigned sequence
> > > number with currval when I am not using a transaction? What
> > > happens when multiple users are inserting at the same time?
> > >
> > > I am trying to create a sequence with out any "missing" numbers. If
> > > there is a failure to insert, and a sequence number is "taken". I want
>
> > > the empty row.
> > >
> > > Thanks, it is getting clearer
> >
> > You really can't use sequences with no gaps. Sequence numbers are not
> > _held_ until commit because it would block other backends trying to get
> > sequence numbers.
> >
> > --
> > Bruce Momjian| http://candle.pha.pa.us
> > [EMAIL PROTECTED] | (610) 853-3000
> > + If your life is a hard drive, | 830 Blythe Avenue
> > + Christ can be your backup.| Drexel Hill, Pennsylvania
> 19026
> >
> > ---(end of broadcast)---
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> >
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: Calling Java from psql (was Re: [SQL] requesting help)
Tom Lane writes: > I'm generally pretty suspicious of any system design that requires > calling outside programs from an SQL function. The problem is that > this fundamentally breaks transactional semantics: if the transaction > is rolled back after the function call, its effects inside the database > disappear ... but there's no way to roll back whatever the outside > program did. Now you have a consistency problem. The trick here is to organize your outside programs into fairly atomic chunks and do conservative error logging. The need to synchronize the non-database world with the database is definitely real. But usually a regular function call near the end of the transaction block is much more appropriate than a trigger function. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Possible 7.1RC1 bug
Here's a fragment of code that works on 7.0.3 but gives a type mismatch on 7.1: Under 7.1RC1, func1 fails and func2 builds OK. The error is: ERROR: return type mismatch in function: declared to return int4, returns numeric It appears that sum(int4) returns type numeric. Shouldn't it return int4? create table ints ( inte int4 ); create function func1() returns int4 as ' select coalesce(sum(inte),0) from ints; ' language 'sql'; create function func2() returns int4 as ' select coalesce(sum(inte)::int4,0) from ints; ' language 'sql'; begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Escaping \
> > I've a problem with escaping a \ in a string. > > > > When I enter the query: > > > > SELECT '''\\\''; I get the right result: '\' > > > > But when I try this in a Function: > > > > CREATE FUNCTION sp_tmp() RETURNS varchar(10) > > AS ' > > SELECT ''\\\' AS RESULT' > > LANGUAGE 'sql'; > > > > I get the following Parse-erros: > > > > psql:tmp1:4: ERROR: Unterminated quoted string. I don't understand why, > > when you leave the SELECT-statement out you get '''\\\'' and that is not > > unterminated. > > Had something similar myself the other day. The reason is that you are > already one deep in Postgres' string-parser, so you need something like: > > SELECT '''' AS RESULT Seems as if this would give \\ I think you need something like SELECT \\\'' AS RESULT In other words, double all the single quotes. (This leads to all kinds of fun stuff with 7.1 and code generating queries ;) -Cedar ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] pg_dump error plus RelationBuildTriggers: 1 record(s) not found for rel links
Hi all, here is error I that am getting: pg_dump -s mondo > mondo.out PQgetvalue: ERROR! tuple number 0 is out of range 0..-1 I faced similar example in the past too. At that time, there was a referentional constraint problem but this situation is different. I am usning postgres 7.0.3 on redhat 6.2 Thanks in advance for your help. Regards begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Re: [SQL] inconsistent functionality with LIKE operator
Phuong Ma <[EMAIL PROTECTED]> writes: > I'm using PostgreSQL version 7.1, and I'm having trouble with the LIKE > statement. How would I find the value "a\bc"? I tried using the > backslash to escape it: LIKE 'a\\b%'; I think you need four backslashes. The string-literal parser eats one level of backslashes, but you need the pattern that arrives at LIKE to look like "a\\b%". BTW, "PostgreSQL version 7.1" does not exist yet. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] inconsistent functionality with LIKE operator
I'm using PostgreSQL version 7.1, and I'm having trouble with the LIKE statement. How would I find the value "a\bc"? I tried using the backslash to escape it: LIKE 'a\\b%'; If I specify: LIKE 'a\\bc', then it works, but if I wanted it to look for consecetive characters after the c, using the %, then it returns other values besides what I'm looking for. I also have the same problem with percent (%). I have a test string with the value "ab%c", and I want to look for that using LIKE. I tried: LIKE 'ab\%c', LIKE 'ab%%', and LIKE 'ab\%_', but these statements not only returned what I was looking for, but also other values: "abc","ABC", and "Abc". Phuong ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
RE: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
> > I don't think that we dare try to make any basic changes in > > MVCC for 7.1 at this late hour, so Forest is going to have > > to live with that answer for awhile. But I would like to see > > a cleaner answer in future releases. > > Is it the MVCC's restriction that each query inside a function > must use the same snapshot ? No. MVCC restricts what is visible to query itself. Current function' behaviour is like Oracle' one. Strictly speaking queries inside function don't use the same snapshot - they see changes made by other queries of this function. Should we allow them see changes made by other transactions? I'm not sure. Maybe by special means like CREATE SNAPSHOT S; SELECT FROM foo WITH SNAPSHOT S; ? For this particular case - concurrent UPDATE then UPDATE/DELETE + SELECT - there is simple solution: meeting tuple updated by concurrent *committed* transaction SELECT (in READ COMMITTED mode) should look in newer tuple versions. If some of newer tuples is invalidated (updated/deleted) by *this* transaction and this invalidation is *visible* to SELECT (older CommandId) then old tuple version must not be returned (newer tuple version will be returned of course). Reported problem is caused by bug (only one tuple version must be returned by SELECT) and this is way to fix it. But note that for the case of concurrent DELETE then INSERT + SELECT two tuples will be returned anyway and I don't think that this is bug. > > As I've opined before, the whole EvalPlanQual mechanism > > strikes me as essentially bogus in any case... > > How would you change it ? UPDATE/SELECT FOR UPDATE have to > SELECT/UPDATE the latest tuples. I don't think of any simple > way for 'SELECT FOR UPDATE' to have the same visibility as > simple SELECT. Yes, I also don't understand what's wrong with EvalPlanQual. Vadim ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] date_part bug?
Hello:
I'm using postgres 7.0.2. When I use date_part('day', date) sometimes I
get wrong values. Ie:
and date_part('day', '1999-3-28')=27
and date_part('day', '2000-3-26')=25
Is it a bug? Is there any SQL equivalent function?
--
Salvador Mainé
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL]
Has anyone tried setting up Postgres as a linked server under Microsofts SQL Server 7 to connect with SQL 6.5 I am able to create the link correctly (see below) and see all the tables available in Postgres, but if I try querying anything in them I get the following error Server: Msg 7313, Level 16, State 1 Invalid schema or catalog specified for provider 'MSDASQL'. how should I correct this problem. I am in very critical position because of this problem. please help me out ASAP i will be thankfull for that. waiting eagarly for the reply with helpful suggetion. regards, Intekhab Alam _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
RE: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
At 09:58 28/03/01 -0800, Mikheev, Vadim wrote: > >Reported problem is caused by bug (only one tuple version must be >returned by SELECT) and this is way to fix it. > I assume this is not possible in 7.1? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
Philip Warner wrote: > > At 19:14 29/03/01 -0800, Mikheev, Vadim wrote: > >> >Reported problem is caused by bug (only one tuple version must be > >> >returned by SELECT) and this is way to fix it. > >> > > >> > >> I assume this is not possible in 7.1? > > > >Just looked in heapam.c - I can fix it in two hours. > >The question is - should we do this now? > >Comments? > > It's a bug; how confident are you of the fix? > I doubt if it's a bug of SELECT. Well what 'concurrent UPDATE then SELECT FOR UPDATE + SELECT' return ? regards, Hiroshi Inoue ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
At 19:14 29/03/01 -0800, Mikheev, Vadim wrote: >> >Reported problem is caused by bug (only one tuple version must be >> >returned by SELECT) and this is way to fix it. >> > >> >> I assume this is not possible in 7.1? > >Just looked in heapam.c - I can fix it in two hours. >The question is - should we do this now? >Comments? It's a bug; how confident are you of the fix? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
> Just looked in heapam.c - I can fix it in two hours. > The question is - should we do this now? This scares the hell out of me. I do NOT think we should be making quick-hack changes in fundamental system semantics at this point of the release cycle. The problem went unnoticed for two full release cycles --- therefore, it can wait another cycle for a fix that has been considered, reviewed, and tested. Let's not risk making things worse by releasing a new behavior we might find out is also wrong. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
At 13:16 30/03/01 +0900, Hiroshi Inoue wrote: >Philip Warner wrote: >> >> At 19:14 29/03/01 -0800, Mikheev, Vadim wrote: >> >> >Reported problem is caused by bug (only one tuple version must be >> >> >returned by SELECT) and this is way to fix it. >> >> > >> >> >> >> I assume this is not possible in 7.1? >> > >> >Just looked in heapam.c - I can fix it in two hours. >> >The question is - should we do this now? >> >Comments? >> >> It's a bug; how confident are you of the fix? >> > >I doubt if it's a bug of SELECT. No idea where the bug is, but SELECT should never return two versions of the *same* row. >'Well what >'concurrent UPDATE then SELECT FOR UPDATE + >SELECT' return ? No idea, maybe Vadim or Tom can help? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
