[SQL] SOME PL/PGSQL PROBLEMS
I found there are some problems with PL/pgSQL. 1) 'Drop Table' doesn't work at all 2) '--' comment causes parsing error 3) Execute doesn't work at all. It always causes parsing error! Some questions 1) Can I use variable in FROM clause for a table name? 2) Can I use variable in WHERE such as xxx = variable? JACK ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Btree index on varchar
Hello, I have a question for Btree indexes on a varchar field. Does postgres (7.0.2) uses a special encoding when creating Btree indexes on varchar?? Is it much more efficient to create indexes on integer than on strings with average length 30 chars?? Thank you in advance for your help Regards Sofia Alexaki ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] creating "job numbers"
Jan,
Thanks, I must be missing something here. Bear with me, I am
trying to form an intelligent question.
Using the serial data type... I don't understand when the backend
would skip a number.
If the db is assigning the number with the insert, then if two (or
more) clients are trying to insert into the db at the exact same time,
only those that are successful should get a number. I am trying to
envision a situation where two clients hit at the same time and
because of problem with the insert, one aborts and the serial data
number is skipped. I would have assumed that the aborted insert is
just skipped no harm done.
I guess that I could then break the insert down into two parts. Insert
only the client name in order to grab the next job number then
update the row. I think I should be able to reduce the number of
aborted inserts to 1 in a couple of thousand were the abort is due to
client input error. (I wanted to say in a million but that just seemed
too far fetched) The only reason that I can think of that would cause
an abort would be that data was currupted in transit to the db. Or the
front end crashed and sent bad data.
Is this reasonable? I feel that people with more expierence might
shed a little light here.
Thanks for your time.
Ted
-Original Message-
From: Jan Wieck <[EMAIL PROTECTED]>
To: postgresql <[EMAIL PROTECTED]>
Date: Thu, 22 Mar 2001 14:48:19 -0500 (EST)
Subject: Re: [SQL] creating "job numbers"
>
> Two possible ways:
>
> 1. If you can live with gaps in the job numbers, you can use
> the serial data type. That is, you create your table like
>
> CREATE TABLE jobs (
> job_id serial PRIMARY KEY,
> employee_id integer REFERENCES staff,
> ...
> );
>
> Now your application can INSERT a row not specifying an
> explicit value for the job_id like
>
> INSERT INTO jobs (employee_id, ...)
> VALUES (4711, ...);
>
> and reading the PostgreSQL assigned job_id back with
>
> SELECT currval('jobs_job_id_seq');
>
> Even if there are other processes doing the same
> concurrently, the assigned job_id is guaranteed to be
> unique and the currval() given back by your database
> connection isn't affected by it.
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Foreign key referencing subclasses.
>> CREATE TABLE resource_record( >> rrid SERIAL >> -- etc. >> ); > There is no primary key for this table. Just write PRIMARY KEY after > SERIAL. There is. I accidentally left it out in the post. > CREATE INDEX soa_record_pkey ON soa_record ( rrid ); > You could also state the referenced field name (you actually have to, > if you happen to reference to a non-primary key field): Ok, thanks. This will work for this case, but I still want to be able to reference resource_record*. Will this be possible in later versions? Inheritance isn't quite as useful if I can't. Sorry to be such a whiner, by the way. PostgreSQL is great. -- johs ---(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] SOME PL/PGSQL PROBLEMS
On 3/23/01, 9:10:16 AM, datactrl <[EMAIL PROTECTED]> wrote regarding [SQL] SOME PL/PGSQL PROBLEMS: Sorry, can't reproduce this at all Jack > I found there are some problems with PL/pgSQL. > 1) 'Drop Table' doesn't work at all No problems in 7.1, can't remember about previous versions > 2) '--' comment causes parsing error No problems ever AFAIK > 3) Execute doesn't work at all. It always causes parsing error! I know this is only in 7.1, not in earlier versions > Some questions > 1) Can I use variable in FROM clause for a table name? No - afraid not. Queries are pre-compiled as I understand it, although this is possible in pltcl (and plperl I think). > 2) Can I use variable in WHERE such as xxx = variable? Yes, exactly as you show > JACK Take a look at http://www.brasileiro.net/postgres/ for some example plpgsql functions or check http://techdocs.postgresql.org or Bruce's book at http://www.postgresql.org/docs/awbook.html If you have a specific example, please post it with the version of PG you are running. HTH - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [SQL] creating "job numbers"
Thanks Micheal,
I guess what I am trying to figure out is, 'how important is this in
reality, in a working setup'. I know that at times I want things to be
perfect, and it is not necessary.
I was thinking about how I do this manually.
I would take a sheet of paper, write numbers down the left column.
make a couple of sheets with increasing numbers. As jobs enter the
shop, I populate the meta data. If a job gets killed, it is marked as
dead but the number does not go away. It could be used again.
What I really need to do is:
Insert "blank" into openjobs;
SELECT currval('jobs_job_id_seq');
UPDATE openjobs "job meta data" where jobno= "returned number
from prev select"
Even if this job fails, I have the empty row. I can choose to put a job
in there later if I want. Or leave it blank. I feel that all the numbers
have to be accounted for as used or null. But it should not skip.
Thanks,
Ted
-Original Message-
From: Michael Ansley
<[EMAIL PROTECTED]>
To: 'postgresql' <[EMAIL PROTECTED]>
Date: Fri, 23 Mar 2001 13:22:09 -
Subject: RE: [SQL] creating "job numbers"
> The procedure is something more like this:
>
> The first backend grabs the sequence number, say 1, and tries to
> insert.
> Whether or not this insert succeeds, the number 1 is gone from
the
> sequence.
> Then backend two tries to insert, and grabs the number 2 from the
> sequence.
> After this, the first backend rolls back, and doesn't insert. The next
> backend will get number 3. And so number 1 is lost. If the
session is
> caching sequence number, then even more numbers may be lost.
Anyway,
> the
> principle is that sequences do not roll back. Once you have a
number,
> it's
> gone, whether or not you use it. This is because keeping track of
> numbers
> to keep them contiguous is a time-consuming exercise, and
causes
> locking
> problems. So, the principle is that a sequence will always give you
a
> distinct number, but not necessarily the next number.
>
> Hope this helps...
>
>
> MikeA
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] creating "job numbers"
"postgresql" <[EMAIL PROTECTED]> writes: > Using the serial data type... I don't understand when the backend > would skip a number. The value returned by a nextval() call will not be returned again by other nextval() calls, even if the surrounding transaction is later rolled back. Agreed, this isn't in line with full transactional semantics, but it was deemed the more useful thing to do precisely because of that. If you want the other behavior you can build it yourself, whereas there's no way to build the actual behavior of sequence objects in plain SQL. The reason why this is more useful is that with this behavior, acquirers of serial numbers don't need to wait for each other. A no-skipped-numbers implementation requires each would-be acquirer to block waiting to see if previous acquirers commit or not. You get no concurrency at all if you build your system like that. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Btree index on varchar
Alexaki Sofia <[EMAIL PROTECTED]> writes: > I have a question for Btree indexes on a varchar field. > Does postgres (7.0.2) uses a special encoding when creating > Btree indexes on varchar?? "Special encoding"? No ... a varchar is a varchar. > Is it much more efficient to create indexes on integer than > on strings with average length 30 chars?? It'd save some space and thereby save I/O time. But I dunno about "much" more efficient. Figuring that there's a dozen or so bytes of index-entry overhead in addition to the value itself, you might be looking at a factor of 3 or so difference in index tuple size, hence about a 3x savings in I/O for index scans; but that wouldn't translate to anything like a factor of 3 for the total operation, since the number of main-table tuples visited wouldn't change. Try it and see, but I'd guess that the net effect would be relatively small. Really what you want to be asking yourself is which columns do you need an index on for your query logic. What datatype they are is a minor consideration. 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])
Re: [SQL] drop table in PL/pgSQL
You cannot CREATE|DROP ALTER table in PL/pgSQL, in general, plsql can only take DML(i.e. SELECT| INSERT|UPDATE..) Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 23 Mar 2001, datactrl wrote: > When I use "drop Table ..." in PL/pgSQL, it always causes an error as > "ERROR copyObject: don't know how to copy 614" > > JACK > > > ---(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 4: Don't 'kill -9' the postmaster
Re: [SQL] creating "job numbers"
postgresql wrote: > Jan, > > Thanks, I must be missing something here. Bear with me, I am > trying to form an intelligent question. > > Using the serial data type... I don't understand when the backend > would skip a number. > If the db is assigning the number with the insert, then if two (or > more) clients are trying to insert into the db at the exact same time, > only those that are successful should get a number. I am trying to > envision a situation where two clients hit at the same time and > because of problem with the insert, one aborts and the serial data > number is skipped. I would have assumed that the aborted insert is > just skipped no harm done. Concurrency will not cause your transactions to abort. It's just if you do BEGIN; INSERT INTO ... ROLLBACK; that the generated sequence numbers don't get rolled back. So you might find job numbers 1, 2, 4 where 3 is missing because it's transaction aborted (explicit rollback or error during processing). The serial data type will never fill in those gaps. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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] SOME PL/PGSQL PROBLEMS
You blame something should not be blamed. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 23 Mar 2001, datactrl wrote: > I found there are some problems with PL/pgSQL. > 1) 'Drop Table' doesn't work at all In general, PLSQL can only take DML instead of DDL. > 2) '--' comment causes parsing error Not true. > 3) Execute doesn't work at all. It always causes parsing error! I havn't tested, I cannot say. > > Some questions > 1) Can I use variable in FROM clause for a table name? No, table name cannot be a variable except in execute statement. > 2) Can I use variable in WHERE such as xxx = variable? Same as 1). > > JACK > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] trigger output to a file
pgsql-sql wrote: > Hello Everyone, > > Here's my simple question. > > I just want to know/get the recent changes made to a table. > Deeper? I wanted the Postgresql server to *publish* every > changes made to a table (similar to replication, incremental transfer, > etc.). > What is the best way to go about it? > > My idea is to create a trigger for every insert/update that will > select the new/updated record and output it to a file. Or better > yet, I would like to output the actual sql query to file. > Is it possible to output the result of an sql query from a trigger > to a file? How? Putting the SQL query to a file would be my approach too. The trigger approach lacks the capability to discard changes already logged in case of a transaction rollback. Thus, I wouldn't buy it. For the query string logging, alot more is required. Not only the queries themself are needed, you'd need to serialize snapshot creation, log sequence allocations and the like. And the program rolling forward this kind of log into another database needs control mechanisms to tell the database that it's in this recovery mode and has to ask for those values in case it needs them. You might guess it already, I've been thinking about it for a year or so now. And I'm still not past the point to start implementing it. > > I would appreciate any answer. Thank you very much. > Sherwin > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] drop table in PL/pgSQL
On Fri, Mar 23, 2001 at 09:52:56AM -0800, Jie Liang wrote: > > You cannot CREATE|DROP ALTER table in PL/pgSQL, > in general, plsql can only take DML(i.e. SELECT| > INSERT|UPDATE..) You can't? I just did (on PG 7.1). AFAIK, you _can_ CREATE/DROP, but you can't roll back. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club|--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Web Developer "Carrier detected." Go to the dentist... ---(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] drop table in PL/pgSQL
Hmm, I didn't know that, this general idea from Orcale plsql, So, I assume that you can SELECT somefield into a_new_table FROM a_old_table in pg 7.1??? Thank you. No DDL can be roll back. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 23 Mar 2001, Roberto Mello wrote: > On Fri, Mar 23, 2001 at 09:52:56AM -0800, Jie Liang wrote: > > > > You cannot CREATE|DROP ALTER table in PL/pgSQL, > > in general, plsql can only take DML(i.e. SELECT| > > INSERT|UPDATE..) > > You can't? I just did (on PG 7.1). > AFAIK, you _can_ CREATE/DROP, but you can't roll back. > > -Roberto > -- > +| http://fslc.usu.edu USU Free Software & GNU/Linux Club|--+ > Roberto Mello - Computer Science, USU - http://www.brasileiro.net > http://www.sdl.usu.edu - Space Dynamics Lab, Web Developer > "Carrier detected." Go to the dentist... > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] trigger output to a file
I haven't given this a lot of thought, so take it with a grain of salt. But my feeling is that publishing such a detailed log isn't the most effective way to do this sort of thing. How about, instead, changing the structure of your database to keep "old" information? Consider, for example, a simple phone book. You might have the following fields: id firstname lastname phone fax just to keep things simple. How about, instead, having two tables: 1.) Records, which ONLY has the id column; and 2.) Data, which has: id rev firstname lastname phone fax you can get what you're looking for by simply JOINing Records and Data. Then, when you want to "change" a record - say, for example, Andrew Perrin moves from Berkeley to Chapel Hill, thereby changing phones from 510-xxx- to 919-xxx- - you actually *add* a new record, with a higher rev, to Data. So, before: id: 0 rev: 1 firstname: Andrew lastname: Perrin phone: 510-xxx- fax: And after: id: 0 rev: 1 firstname: Andrew lastname: Perrin phone: 510-xxx- fax: id: 0 rev: 2 firstname: Andrew lastname: Perrin phone: 919-xxx- fax: SELECTing the highest rev will give you current data; selecting everything for id 0 sorted by rev will give you the change history. Just a thought. Andy Perrin -- 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 Fri, 23 Mar 2001, Jan Wieck wrote: > pgsql-sql wrote: > > Hello Everyone, > > > > Here's my simple question. > > > > I just want to know/get the recent changes made to a table. > > Deeper? I wanted the Postgresql server to *publish* every > > changes made to a table (similar to replication, incremental transfer, > > etc.). > > What is the best way to go about it? > > > > My idea is to create a trigger for every insert/update that will > > select the new/updated record and output it to a file. Or better > > yet, I would like to output the actual sql query to file. > > Is it possible to output the result of an sql query from a trigger > > to a file? How? > > Putting the SQL query to a file would be my approach too. > > The trigger approach lacks the capability to discard changes > already logged in case of a transaction rollback. Thus, I > wouldn't buy it. > > For the query string logging, alot more is required. Not only > the queries themself are needed, you'd need to serialize > snapshot creation, log sequence allocations and the like. And > the program rolling forward this kind of log into another > database needs control mechanisms to tell the database that > it's in this recovery mode and has to ask for those values in > case it needs them. > > You might guess it already, I've been thinking about it for a > year or so now. And I'm still not past the point to start > implementing it. > > > > > I would appreciate any answer. Thank you very much. > > Sherwin > > > > > > ---(end of broadcast)--- > > TIP 6: Have you searched our list archives? > > > > http://www.postgresql.org/search.mpl > > > > > -- > > #==# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #== [EMAIL PROTECTED] # > > > > _ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] how do I check if a temporary table exists?
How do I check if a temporary table exists? Searching pg_tables with a temporary table name we chose always fails. JACK ---(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
[SQL] Serials.
Please see below for my table schema. I have two questions.
(1) Why is a sequence limited to 2147483647, it seems very small?
(2) If I reset the sequence, then try another insert. It will not insert
anything until it cycles through all sequences and finds an unused one. It
will give the following error each time it tries to insert a row with a
used sequence:
PostgreSQL query failed: ERROR: Cannot insert a duplicate key into unique
index releases_pkey
How can I possibly get around this issue so that I can be sure to always
have a free id without getting the error above?
Thankyou.
-- TABLE --
=# \d bookings
Table "bookings"
Attribute | Type| Modifier
--+---+---
id | integer | not null default
nextval('bookings_id_seq'::text)
added| timestamp | not null
client_id| smallint | not null
booking_time | timestamp | not null
duration | float4| not null
notes| text | not null
Index: bookings_pkey
=#
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
