Re: [GENERAL] sequences vs oids as primary keys
craigp wrote: i had some questions/thoughts on using sequences vs oids as pks... it's a common requirement to obtain the last value of a sequence after an insert. most other databases support such a feature, but since oid's are deprecated, and only oid's are returned, this isn't supported by postgres (and performance suffers from having to make an extra sql call). Really? What percentage drop in your transaction rate are you seeing from calling nextval()? Does the load vary appreciably with the number of concurrent clients? 1) does it make sense (and would it be possible) to make a rule which would, say, somehow write into the oid field of a tuple to be returned by lastoid? i'm assuming here that the database would not have oid's enabled. I'm not sure I understand what you mean here. 2) if not, can the C code be modified to support this (maybe ExecInsert())? basically, if oid's are disabled for a given relation, and a sequence is the primary key, then instead of returning InvalidOid return the current sequence value (only for inserting a single row, possibly only if the db has been configured to support that, etc etc). Hmm - you're probably better off seeing if any work has been done on INSERT ... RETURNING (I think that's the syntax). I seem to remember someone mentioning it on the hackers list. 3) if not that, would it make sense to enable applications to reserve a pool of sequence numbers? say, some kind of sequence.reserve(int count) function which takes the number of id's to reserve and returns the start of the sequence, where all the sequence #'s in [start, start + count -1] are guaranteed to be contiguous (or just overload nextval). If you manually create a sequence, you can set an INCREMENT for nextval() which sounds like what you want. 4) maybe there's a better way of handling this? Why are you fetching an auto-generated id value? What meaning does it have for you? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] What about pgtclsh
It's here now. I think it used to be in the main distro, but has been moved out recently. http://pgfoundry.org/projects/pgtcl/ On 24 Jul 2006 03:07:59 -0700, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Hello everybody, I must replace an old server by a new one, and I decide to upgrade postgresql 7.1.3 to 8.1.4. But a lot of web script use a program name pgtclsh. I try to find somes informations about it on the web, but all the information that I find was for the older version thant 8. Someone have a idea to install pgtclsh for Postgresql 8.1.4 ? Thx a lot. Maxime ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] error code when database does not exist
Title: [JDBC] Invalid column display size. Cannot be less than zero i want to know what error code will be returned if the database does not exist. i have the following list, howvere do not know which one shall be returned when database itself does not exits http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html thanks, regards Surabhi From: [EMAIL PROTECTED] on behalf of alanSent: Tue 7/25/2006 4:12 PMTo: pgsql-jdbc@postgresql.orgSubject: [JDBC] Invalid column display size. Cannot be less than zero ***Your mail has been scanned by InterScan VirusWall.***-***Hi all!I'm using postgresql-8.1-407.jdbc3.jar.The following code:CachedRowSet cachedRowSet = new CachedRowSetImpl();cachedRowSet.populate(resultSet);raise the following exception:java.sql.SQLException: Invalid column display size. Cannot be less thanzero atjavax.sql.rowset.RowSetMetaDataImpl.setColumnDisplaySize(RowSetMetaDataImpl.java:267) atcom.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:702) at com.sun.rowset.CachedRowSetImpl.populate(CachedRowSetImpl.java:620) at com.mainfield.test.TestJoinRowSet.run(TestJoinRowSet.java:47) at com.mainfield.test.TestJoinRowSet.main(TestJoinRowSet.java:71)Last informations available about this issue it's on this mailing listone year ago.Someone has news about this issue?Many thanks!Regards.---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Archiving wal files
Hi list, somehow it seems that in my pg_xlog directory not all wal files are deleted after copying them to the archive directory even though the archive_status is done for all of them. Is it safe to delete them after they were copied or is it safe to use 'mv' as archive_command? Thanks! -- Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Beginner's questions about creating a custom data type in PostgreSQL...
On 7/25/06, Redefined Horizons <[EMAIL PROTECTED]> wrote: Merlin, I am actually working on a spatial extension for PostgreSQL that is similar to PostGIS, but compatible with a geometry library I am developing. I had first thought of using a "table" only desgin, with no custom data types, to store the geometry information in the database. For example, the "many_lines" table might store records representing multi-segmented lines. (One geometry made up of smaller connected line segments.) A separate table called "single_lines" would contain the actual lines. The link between the line segment geometries and the "many lines" geometries would be maintained by a one-to-many relationship. ah, that changes things completely. If you are going the custom route, I would try this approach first and discard it only when you have determined to to be not fast enough. the major problems here are going to be inefficient data size and problems with spatial indexing. postgis makes use of the gist index and if you want fast spatial indexing you will need to generate index ops for any custom type regardless of it's implementation. this means you have to have a pretty good understanding of how gist works and how the postgresql type system works. to be perfectly honest, if you pull this off and make it work reasonably efficient, you should be giving me advice, not vice versa :) have you considered implementing postgis and cutting some c code to do the type translation on the backend? However, I began to realize that as my geometries became more complex I would begin to have more and more interdependent relationships. this in itself isn't a problem, until you consider that in a typical GIS system I might be dealing with thousands or tens of thousands of geometries. In the example above, if I wanted to find all of the line segment geometries that belonged to a"many line" geometry I would have to run a query on the "single_lines" table. I am concerned about the performance of a system that would frequently rely on a cascade of these types of searches. If I instead implement a custom "many lines" geometry data type I can now directly access the line segment geometries. Do you think this reasoning is sound? Are custom data types the right solution? Or am I being overly concerned about the performance issues? that really depends on your requriements. you are asking some really broad questions. generally though, in GIS environments performance is extremely relevant and you need to carefully consider your implementation approach. the good news is postgresql is the right place to be. If custom data types aren't a good idea in this situation, when are they? I can think of almost no situation when I can't mirror the functionality of a custom data type with a series of related database tables. I am differentiating between custom and composite types. composite types are rarely worthwhile although I do use them sometimes to pass data in and out of functions. I suggest reading the composite types chapter of the documentation and making your own determination. they are analgous to anonymous 'C' structs as opposed to typedef'd C structs which are closer to tables. custom types OTOH are worthwhile if you are filling a need that is not served by an existing type. if you want a good example on implementing a custom type check out the 'cube' example in contrib and also 'earthdistance' which shows how the cube might be implemted in gist scenarios. you will also get a good feel for the complexity involved, including but not limited to custom grammars, backend coding, and deep knowledge of the postgresql type system. It could also be a great learning experience. regards, merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Lock changes with 8.1 - what's the right lock?
> Did you try the example I posted? What happens if you do the > following? First set up a couple of tables: I no longer have 7.4 running. I'll have to check and see if can test. > Is it possible that your foreign key constraints are DEFERRED in > 7.4 but not in 8.1? That would change transaction behavior that > depends on locks. No, there was no change to the database other than to start using table spaces in 8.1. Previously when multiple jobs were running concurrently, they'd block while updating 'addresses', then take off and complete - unless they hit a foreign key that someone else had referenced in which case they'd block again. Now one job waits at the initial addresses lock until the other is completely done running (i.e. not updating tables that reference addresses). > An ordinary SELECT acquires ACCESS SHARE, which conflicts only with > the strongest lock, ACCESS EXCLUSIVE. If you're modifying a table > that has a foreign key constraint then 8.1 does a SELECT FOR SHARE > on the referenced table and earlier versions do a SELECT FOR UPDATE; > both cases acquire ROW SHARE on the referenced table. Two lock > types are stronger than SHARE ROW EXCLUSIVE: EXCLUSIVE and ACCESS > EXCLUSIVE, both of which conflict with ROW SHARE. So instead of > saying that SHARE ROW EXCLUSIVE is the weakest lock that meets your > requirements, I should have said that it's the only lock that does. That's what I thought from reading the manual, but wasn't sure. Thanks. I found the information that led me to believe the locking was fixed in 8.1. The problem I had with 7.x was the 'deadlock detected' if the foreign keys weren't referenced in sorted order, as the records were locked with more than a share lock. >> I haven't seen anything to indicate that 8.x improves foreign key >> refererence locking and fixes the foreign key 'deadlock detected' issue. >> Has that been addressed in 8.1? > > 8.1 should be using the new shared row locks for doing the checks. This > should fix the case mentioned. > >> I've never quite understood why a READ of a record with a foreign key >> reference results in the referenced record being locked with more than a >> shared lock. > > Up until now, we didn't have one to get on a per-record basis. and >> So, until 8.1 PostgreSQL had "something better than row-level locking" for >> some things, but no row locking when needed? Or was it row locking is >> there, but just no shared row locking? > > The latter, the row locks before were single owner and were such that a > second lock request for the same row would wait for the first to be > released. Now effectively you have two levels of locks at the row level, > the weaker of which conflicts with the stronger but not with itself. The > thing about MVCC is that readers do not have to get either lock if they > aren't trying to prevent modifications. Wes ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] CASE statement and SETOF values
Am Dienstag, 25. Juli 2006 00:01 schrieb Stephan Szabo: > The above basically looks like: > CASE WHEN THEN ELSE > END. > > In SQL92 at least, the form of which looks like (SELECT > ...) is which is limited to 1 column and 1 row. The > other subquery forms don't look legal in that position unless they changed > that in a later version of the spec. Ok, and is there any way to circumvent this problem? CU Christian ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Archiving wal files
Christian Rengstl wrote: > Hi list, > > somehow it seems that in my pg_xlog directory not all wal files are > deleted after copying them to the archive directory even though the > archive_status is done for all of them. Is it safe to delete them > after they were copied or is it safe to use 'mv' as archive_command? No, it is not safe nor necessary. Leave them alone -- Postgres will automatically reuse them as needed. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: 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: [GENERAL] Lock changes with 8.1 - what's the right lock?
On Tue, Jul 25, 2006 at 07:12:28AM -0500, Wes wrote: > > Unless I'm misunderstanding you or a bug was fixed between 7.4.5 > > and 7.4.13 (the version I'm running), I'm not convinced that last > > statement is true. EXCLUSIVE conflicts with all lock types except > > ACCESS SHARE; foreign key references prior to 8.1 use SELECT FOR > > UPDATE and in 8.1 they use SELECT FOR SHARE, but in both cases they > > acquire ROW SHARE on the referenced table, which conflicts with > > EXCLUSIVE. > > My apologies for being so unclear. I had intended to just indicate that the > problem occurred when we upgraded from 7.4.5 to 8.1, as opposed to this > being fixed specifically in 8.1. I didn't realize this was fixed in a 7.4 > release - I thought it was 8.x. The last time I'd checked, it hadn't been > fixed in 7.x. New in 8.1 is that foreign key references use SELECT FOR SHARE instead of SELECT FOR UPDATE, but in all versions the acquired locks conflict with EXCLUSIVE. I see nothing in the Release Notes indicating that that behavior changed between 7.4.5 and 7.4.13. Did you try the example I posted? What happens if you do the following? First set up a couple of tables: CREATE TABLE foo (id integer PRIMARY KEY); CREATE TABLE bar (fooid integer NOT NULL REFERENCES foo); INSERT INTO foo VALUES (1); Then open two connections to the database and execute the steps in T1 in one connection and then the steps in T2 in the other connection: T1: BEGIN; T1: INSERT INTO bar VALUES (1); T2: BEGIN; T2: LOCK TABLE foo IN EXCLUSIVE MODE; The T2 connection should block until you execute COMMIT or ROLLBACK in T1. What happens on your system? Does this approximate what you're doing? Is it possible that your foreign key constraints are DEFERRED in 7.4 but not in 8.1? That would change transaction behavior that depends on locks. > > SHARE ROW EXCLUSIVE is the weakest lock that meets these requirements. > > It conflicts with itself (#2) and with ROW EXCLUSIVE, which UPDATE, > > DELETE, and INSERT acquire (#1), but doesn't conflict with ROW SHARE, > > which is what SELECT FOR UPDATE/SHARE acquire (#3). > > Thanks for the confirmation. Is there any stronger lock that would not > block SELECT foreign key references? I didn't find any documentation on > what type of lock is grabbed by a when a foreign key is referenced during > SELECT (or other). An ordinary SELECT acquires ACCESS SHARE, which conflicts only with the strongest lock, ACCESS EXCLUSIVE. If you're modifying a table that has a foreign key constraint then 8.1 does a SELECT FOR SHARE on the referenced table and earlier versions do a SELECT FOR UPDATE; both cases acquire ROW SHARE on the referenced table. Two lock types are stronger than SHARE ROW EXCLUSIVE: EXCLUSIVE and ACCESS EXCLUSIVE, both of which conflict with ROW SHARE. So instead of saying that SHARE ROW EXCLUSIVE is the weakest lock that meets your requirements, I should have said that it's the only lock that does. Hopefully I've understood what you're asking; if not then please clarify. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] gmake Errors: pg_backup_archiver.c: undefined reference to `lo_create'
Emi Lu <[EMAIL PROTECTED]> writes: > I did make distclean, and I retried "configuration and ", but I still > saw the following errors: The only other theory I can think of is that the linker is picking up an old libpq.so from /home/postgresql/lib because of the -rpath switch ... which it should not be doing, but maybe it is anyway. If there's an 8.0 libpq.so there, try getting rid of it. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] sequences vs oids as primary keys
craigp wrote: 1) does it make sense (and would it be possible) to make a rule which would, say, somehow write into the oid field of a tuple to be returned by lastoid? i'm assuming here that the database would not have oid's enabled. We do this in a trigger. We assign the NEXTVAL to a variable, write that to the row, then raise its value as a notice. Then we just retrieve the notice. begin:vcard fn:Kenneth Downs n:Downs;Kenneth email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Lock changes with 8.1 - what's the right lock?
> Unless I'm misunderstanding you or a bug was fixed between 7.4.5 > and 7.4.13 (the version I'm running), I'm not convinced that last > statement is true. EXCLUSIVE conflicts with all lock types except > ACCESS SHARE; foreign key references prior to 8.1 use SELECT FOR > UPDATE and in 8.1 they use SELECT FOR SHARE, but in both cases they > acquire ROW SHARE on the referenced table, which conflicts with > EXCLUSIVE. My apologies for being so unclear. I had intended to just indicate that the problem occurred when we upgraded from 7.4.5 to 8.1, as opposed to this being fixed specifically in 8.1. I didn't realize this was fixed in a 7.4 release - I thought it was 8.x. The last time I'd checked, it hadn't been fixed in 7.x. >> What is now the appropriate lock? It needs to: >> >> 1. Prevent others from updating the table >> 2. Block other jobs that are requesting the same lock (if job 2 does a >> SELECT and finds nothing, it will try to create the record that job 1 may >> already have created in its transaction). >> 3. Not conflict with foreign key reference locks > > SHARE ROW EXCLUSIVE is the weakest lock that meets these requirements. > It conflicts with itself (#2) and with ROW EXCLUSIVE, which UPDATE, > DELETE, and INSERT acquire (#1), but doesn't conflict with ROW SHARE, > which is what SELECT FOR UPDATE/SHARE acquire (#3). Thanks for the confirmation. Is there any stronger lock that would not block SELECT foreign key references? I didn't find any documentation on what type of lock is grabbed by a when a foreign key is referenced during SELECT (or other). Wes ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] CASE statement and SETOF values
On Tue, 25 Jul 2006, Christian Schoenebeck wrote: > Am Dienstag, 25. Juli 2006 00:01 schrieb Stephan Szabo: > > The above basically looks like: > > CASE WHEN THEN ELSE > > END. > > > > In SQL92 at least, the form of which looks like (SELECT > > ...) is which is limited to 1 column and 1 row. The > > other subquery forms don't look legal in that position unless they changed > > that in a later version of the spec. > > Ok, and is there any way to circumvent this problem? Well, the easiest one is to use a procedural language to get conditional statements. For example, something like the following (untested) plpgsql body: DECLARE r record BEGIN IF (some_condition) THEN FOR r IN SELECT ... LOOP RETURN NEXT r; END LOOP; ELSE FOR r IN SELECT ... LOOP RETURN NEXT r; END LOOP; END IF; RETURN; END; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Splitting Timestamps
I have several columns in my database that are timestamps. My developers are asking me how to split the timestamp so that they can look at either the date or at the time portion.I know I can do a select to_date(now(),'-mm-dd') and it will return the date. However, how do I get the time? Also, is this the proper way to get the date portion of a timestamp? Thanks,Chris
Re: [GENERAL] Splitting Timestamps
Chris Hoover wrote: > I have several columns in my database that are timestamps. My developers > are asking me how to split the timestamp so that they can look at either the > date or at the time portion. > > I know I can do a select to_date(now(),'-mm-dd') and it will return the > date. However, how do I get the time? Also, is this the proper way to get > the date portion of a timestamp? select now()::timetz; select now()::time; select now()::date; -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Splitting Timestamps
am 25.07.2006, um 12:54:35 -0400 mailte Chris Hoover folgendes: > I have several columns in my database that are timestamps. My developers > are asking me how to split the timestamp so that they can look at either > the > date or at the time portion. The CAST-Operater is your friend: est=# select now(); now --- 2006-07-25 19:12:36.744262+02 (1 row) test=# select now()::time; now - 19:12:41.803128 (1 row) test=# select now()::date; now 2006-07-25 (1 row) test=# HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Splitting Timestamps
I believe you would want to cast the field to a date like so select datefield::datefrom table1 or select datefield::time from table1. From: Chris Hoover [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 11:55 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Splitting Timestamps I have several columns in my database that are timestamps. My developers are asking me how to split the timestamp so that they can look at either the date or at the time portion. I know I can do a select to_date(now(),'-mm-dd') and it will return the date. However, how do I get the time? Also, is this the proper way to get the date portion of a timestamp? Thanks, Chris
Re: [GENERAL] CASE statement and SETOF values
Am Dienstag, 25. Juli 2006 17:56 schrieben Sie: > You could use a procedural language like plpgsql. Ok, using the plpgsql approach I tried this: CREATE FUNCTION my_function(int4) RETURNS SETOF int8 AS $BODY$ DECLARE myrow RECORD; BEGIN IF (some_condition) THEN FOR myrow IN SELECT ... -- some select statement LOOP RETURN NEXT myrow."foocolumn"; END LOOP; ELSE FOR myrow IN SELECT ... -- some select statement LOOP RETURN NEXT myrow."foocolumn"; END LOOP; END IF; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; But creating this function fails, because it's "missing a LOOP" statement. What am I missing? CU Christian ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Using currval() in an insert statement...
I'm having trouble figuring out how to use the currval() function for
sequences in an INSERT statement. I did some searching online, but
couldn't find what I was looking for.
I have two Schemas in my Database:
metadata
geometry
I have a table named "metadata_geometries" in the metadata schema.
I have a table named "geometry_polaris_numbers" in the geometry schema.
Both tables have bigserial columns set up as primary keys.
There is a one-to-one relationship between records in the
metadata_geometries table and the geometry.polaris_numbers table.
Here is what I'd like to do in my query:
Insert a value into the "metadata.metadata_geometries" table.
Select the value just inserted in to the primary key column,
"pk_dbuid" of that table, and insert it into the
"geometry.polaris_numbers" table in the "metadata" column.
I had the query set up like this:
INSERT INTO metadata.metadata_geometries (value)
VALUES ('This is a test.');
INSERT INTO geometry.polaris_numbers (pnuid, number_of_digits, error,
value, metadata)
VALUES (2305, 7, 100, 1000, currval(metadata.metadata_geometries.pk_dbuid);
However, when I try and execute the query I get the following error message:
ERROR: Missing FROM-clause for table "metadata_geometries"
I know it is the second INSERT statement in this query that is giving
me problems, because the first statement executes by itself without a
hitch.
I think I have the syntax for the currval() call incorrect.
Can anyone help me out with the correct syntax?
Thanks,
Scott Huey
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Plperl return_next and bytea binary data?
[EMAIL PROTECTED] wrote:
> Martijn van Oosterhout wrote:
>
>> On Mon, Jul 24, 2006 at 11:43:39AM +0200, Philippe Lang wrote:
>>> The problem seems to come from the return_next, in conjunction with
>>> binary data:
>>>
>>>
>>> return_next
>>> (
>>> {
>>> val => $_[0] * $_[1],
>>> image => $im->gif()
>>> }
>>> );
>>>
>>
>> I don't know exact how pl/perl works, but given that it has no idea
>> what type the data is, chances are it's passing it through
>> cstring-to-text conversion. You probably want to force it to return
>> bytea or some such (without going through cstring-to-bytea conversion
>> hopefully). I don't see a way to do it in the documentation though...
>
> Casting $im->gif() to bytea with $im->gif()::bytea does not
> help. It even makes things slightly worse: the image returned
> (ethereal sniff) is completely empty, where before it was
> filled with the first characters "GIF89ad" of the image.
>
> Still searching...
I finally found a solution to my problem by using a base64 encoded gif image,
in conjunction with a text column:
--
CREATE OR REPLACE FUNCTION test_gd
(
IN aint4,
IN bint4,
OUT val int4,
OUT image text
)
RETURNS SETOF record
AS
$$
use GD;
use MIME::Base64::Perl;
$im = new GD::Image(100,100);
$white = $im->colorAllocate(255,255,255);
$black = $im->colorAllocate(0,0,0);
$red = $im->colorAllocate(255,0,0);
$im->transparent($white);
$im->rectangle(0,0,89,89,$black);
$im->arc(50,30,95,75,0,360,$black);
$im->fill(50,50,$red);
$image = $im->gif();
return_next
(
{
val => $_[0] * $_[1],
image => encode_base64($image, '')
}
);
return undef;
$$
LANGUAGE 'plperlu';
--
For the small technical drawings this system is suppose to handle, this is just
fine.
Juste one more question: what is the maximum size of a TEXT column in PG 8.1.4?
---
Philippe Lang
Attik System
smime.p7s
Description: S/MIME cryptographic signature
Re: [GENERAL] gmake Errors: pg_backup_archiver.c: undefined reference
I did make distclean, and I retried "configuration and ", but I still saw the following errors: The only other theory I can think of is that the linker is picking up an old libpq.so from /home/postgresql/lib because of the -rpath switch ... which it should not be doing, but maybe it is anyway. If there's an 8.0 libpq.so there, try getting rid of it. You are absolutely correct. It is because of the libpg.so. I used the central gcc before, and -rpath was linked to the 8.0 libpg.so. Now I specified the local gcc and installed version 8.1.4 successfully this time. Thanks a lot for all your kind help! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Splitting Timestamps
On Tue, 2006-07-25 at 12:54 -0400, Chris Hoover wrote:
> I know I can do a select to_date(now(),'-mm-dd') and it will
> return the date. However, how do I get the time?
Casting is the better option, but the to_date format spec handles a lot
more than just dates. See:
http://www.postgresql.org/docs/8.1/interactive/functions-formatting.html
The casting way:
[EMAIL PROTECTED]> select '2006-07-25 21:24'::time;
time
--
21:24:00
[EMAIL PROTECTED]> select '2006-07-25 21:24'::date;
date
2006-07-25
The to_char way:
[EMAIL PROTECTED]> select to_char(now(),'HH24:MI');
to_char
-
10:44
Or the baroque way for your, ahem, timeless applications:
[EMAIL PROTECTED]> select to_char('2006-07-25 20:24'::timestamp,'MI
"minutes" past the HH24th hour');
to_char
---
24 minutes past the 20th hour
[EMAIL PROTECTED]> select to_char('2006-07-25 21:24'::timestamp,'MI
"minutes" past the HH24th hour');
to_char
---
24 minutes past the 21st hour
-Reece
--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Splitting Timestamps
[EMAIL PROTECTED] ("Chris Hoover") writes:
> I have several columns in my database that are timestamps. My
> developers are asking me how to split the timestamp so that they can
> look at either the date or at the time portion. I know I can do a
> select to_date(now(),'-mm-dd') and it will return the date.
> However, how do I get the time? Also, is this the proper way to get
> the date portion of a timestamp? Thanks, Chris
Look at the function date_part(time_type, timestamp)
--
(format nil "[EMAIL PROTECTED]" "cbbrowne" "ntlug.org")
http://www3.sympatico.ca/cbbrowne/lsf.html
Do not worry about the bullet that has got your name on it. It will
hit you and it will kill you, no questions asked. The rounds to worry
about are the ones marked: TO WHOM IT MAY CONCERN.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Plperl return_next and bytea binary data?
Philippe Lang wrote: > Juste one more question: what is the maximum size of a TEXT column in > PG 8.1.4? A handful of bytes less than 1 GB. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Does a GRANT on a table cascade/implied to its SEQUENCES
Tom Lane wrote: "Roderick A. Anderson" <[EMAIL PROTECTED]> writes: I've add a new user and as I go though granting various accesses to the different tables I realized many of those tables have primary keys that are generated by a sequence. Do I need to grant access on the sequences and what type of access -- SELECT for sure but what about UPDATE -- for each of the tables I've granted the user access to? Right now, GRANT on a table doesn't do anything about subsidiary sequences. (There have been discussions about changing that, but nothing's happened yet.) So if you want someone to be able to INSERT into a table with a serial column, you need to give them UPDATE rights on the sequence. Offhand I see no direct reason why they'd need SELECT rights on the sequence, but maybe they do. Thanks for the clarification Tom. Rod -- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Plperl return_next and bytea binary data?
Alvaro Herrera wrote: > Philippe Lang wrote: > >> Juste one more question: what is the maximum size of a TEXT column in >> PG 8.1.4? > > A handful of bytes less than 1 GB. It should be ok then... :) --- Philippe Lang Attik System smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] ECPG. Badly stuck
Hi ,Forget about cursors. Singleton queries are also not working.Tried writing the follwoing code but doesn't give me any result. Don't know if i have to do somethin else apart from what i've already done.
include EXEC SQL INCLUDE sqlca;int main (){ EXEC SQL BEGIN DECLARE SECTION; char movie_type[20]; char *movie_title=NULL; char query_string[256];
EXEC SQL END DECLARE SECTION; EXEC SQL CONNECT TO postgres; EXEC SQL INSERT INTO films VALUES (13,'',4,'2002-2-3','AA','01:01:01'); EXEC SQL SELECT title INTO :movie_type FROM films;
printf("enter the type of movie %s",movie_type); EXEC SQL COMMIT;}Please let me know whats going wrong.I'm compiling my code as follows:[EMAIL PROTECTED]
~]# ecpg ecpg_test.pgc[EMAIL PROTECTED] ~]# gcc -o jas ecpg_test.c -lecpg -L/usr/lib/pgsqlRegards,JasOn 7/23/06, John Smith <
[EMAIL PROTECTED]> wrote:
Simple:
EXEC SQL select foo from bar into :foo;
printf("foo is %s", foo);
If the select statement will return more than one row, then
yes, you need to define a cursor to access it one row at a
time.
John.
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
On Behalf Of Jasbinder
BaliSent: 22 July 2006 18:47To:
pgsql-general@postgresql.orgSubject: [GENERAL]
ECPG. Badly
stuck
Hi,
I had posted this question a few days back.
Sending the same question again.
I have a C program and have some ECPG code in it.
How do i display the data that i retrieve from the postgres database using
a simple select statment or calling a stored procedure.
Is it mandatory to use cursor or there's any other way to print the
retrieved data..
Any kind of help would be highly appreciated.
Thanks,
~Jas
Re: [GENERAL] Using currval() in an insert statement...
On Tue, 2006-07-25 at 10:45 -0700, Redefined Horizons wrote:
> I'm having trouble figuring out how to use the currval() function for
> sequences in an INSERT statement. I did some searching online, but
> couldn't find what I was looking for.
>
> I have two Schemas in my Database:
>
> metadata
> geometry
>
> I have a table named "metadata_geometries" in the metadata schema.
> I have a table named "geometry_polaris_numbers" in the geometry schema.
>
> Both tables have bigserial columns set up as primary keys.
>
> There is a one-to-one relationship between records in the
> metadata_geometries table and the geometry.polaris_numbers table.
>
> Here is what I'd like to do in my query:
>
> Insert a value into the "metadata.metadata_geometries" table.
>
> Select the value just inserted in to the primary key column,
> "pk_dbuid" of that table, and insert it into the
> "geometry.polaris_numbers" table in the "metadata" column.
>
> I had the query set up like this:
>
> INSERT INTO metadata.metadata_geometries (value)
> VALUES ('This is a test.');
>
> INSERT INTO geometry.polaris_numbers (pnuid, number_of_digits, error,
> value, metadata)
> VALUES (2305, 7, 100, 1000,
> currval(metadata.metadata_geometries.pk_dbuid);
>
> However, when I try and execute the query I get the following error message:
>
> ERROR: Missing FROM-clause for table "metadata_geometries"
You need to give currval the name of the sequence that is being
incremented.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] Mapping/DB Migration tool
Hi, I'm looking to migrate psql db1 to a psql db2 that has a different structure even though 70% would be the same. I'd need a tool that allows me to map field to field and then migrate the data from one db to another. Needless to say that preserving data integrity is very important. Any good idea what would be the best approach ? Thanks, Constantin http://www.goodstockimages.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Using currval() in an insert statement...
Try it out the following way:create table test (var1 int);create sequence s1;select s1.nextval;insert into test values (s1.currval);select * from test;Thanks,Shoaib Mir
EnterpriseDB (www.enterprisedb.com)On 7/25/06, Redefined Horizons <
[EMAIL PROTECTED]> wrote:I'm having trouble figuring out how to use the currval() function for
sequences in an INSERT statement. I did some searching online, butcouldn't find what I was looking for.I have two Schemas in my Database:metadatageometryI have a table named "metadata_geometries" in the metadata schema.
I have a table named "geometry_polaris_numbers" in the geometry schema.Both tables have bigserial columns set up as primary keys.There is a one-to-one relationship between records in themetadata_geometries table and the
geometry.polaris_numbers table.Here is what I'd like to do in my query:Insert a value into the "metadata.metadata_geometries" table.Select the value just inserted in to the primary key column,
"pk_dbuid" of that table, and insert it into the"geometry.polaris_numbers" table in the "metadata" column.I had the query set up like this:INSERT INTO metadata.metadata_geometries
(value)VALUES ('This is a test.');INSERT INTO geometry.polaris_numbers (pnuid, number_of_digits, error,value, metadata)VALUES (2305, 7, 100, 1000, currval(metadata.metadata_geometries.pk_dbuid);
However, when I try and execute the query I get the following error message:ERROR: Missing FROM-clause for table "metadata_geometries"I know it is the second INSERT statement in this query that is giving
me problems, because the first statement executes by itself without ahitch.I think I have the syntax for the currval() call incorrect.Can anyone help me out with the correct syntax?Thanks,
Scott Huey---(end of broadcast)---TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Mapping/DB Migration tool
Hello, MC. Only human can do this. Moreover, if "data integrity is very important". Regards You wrote: MM> Hi, MM> I'm looking to migrate psql db1 to a psql db2 that has a different structure MM> even though 70% would be the same. I'd need a tool that allows me to map MM> field to field and then migrate the data from one db to another. Needless to MM> say that preserving data integrity is very important. MM> Any good idea what would be the best approach ? MM> Thanks, MM> Constantin MM> http://www.goodstockimages.com MM> ---(end of MM> broadcast)--- MM> TIP 4: Have you searched our list archives? MM>http://archives.postgresql.org -- With best wishes, Pavel mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Mapping/DB Migration tool
On Jul 25, 2006, at 2:59 PM, MC Moisei wrote: I'm looking to migrate psql db1 to a psql db2 that has a different structure even though 70% would be the same. I'd need a tool that allows me to map field to field and then migrate the data from one db to another. Needless to say that preserving data integrity is very important. I'm not aware of any migration tool to help on this (other than a good SQL editor ;). It seems the best approach is to load an exact copy of db1 and then write a script to transform it into db2, creating, dropping, and altering the structure as necessary. I think there are some tools (EMS?) that can compare schemas and generate the SQL to transform db1 to db2. But there is a good chance an automated approach like this will not transform data the way you want. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] ECPG usage
>while(1)
>{
>EXEC SQL FETCH IN c_movie INTO :movie_title;
> printf("%s\n",movie_title);
> movie_title = NULL;
>}
>
> free (movie_title);
As a side note, you should free movie_title each time, not just set it
to NULL if you use auto allocation.
> At while loop, it keeps on looping forever and my table has 2 records for
> the query that is eventually build.
> Does that mean EXEC SQL WHENEVER NOT FOUND DO BREAK; is not
> working.
No, that would be the first time I hear about this. You could run your
program with debugging enabled (see test/*.pgc for example). Also I'd
like to know which version of ecpg you're using.
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Mapping/DB Migration tool
On Tue, 2006-07-25 at 13:59 -0500, MC Moisei wrote:
I'm looking to migrate psql db1 to a psql db2 that has a different structure
even though 70% would be the same.
Depending on how much the structure changes (as opposed to more trivial things like column names), you might consider whether you could actually use the database itself to do this.
For some kinds of changes, and especially those that make destructive in-place changes that might require debugging, I've written views which generate the SQL statements to execute. I then do something like:
$ psql -Atc 'select sql from sql_changes' | psql -Xa
This works particularly well when the changes can be computed in some way from the database, such as creating indexes for unindexed PKs (postgresql doesn't require indexes on PKs).
You'd probably want to do this by making copies of the original database as a template ('create database db2 template db1') or createdb -T .
-Reece
--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
Re: [GENERAL] Mapping/DB Migration tool
The main thing is I changed a bunch of date types to timestamp type. Is
there a simple way to change the type on such fields ?
Some fields got renamed and the type changed.
It sounds like doing a copy|template of db1 as db2 and then applying the new
changes as a script that will probably work. The contraints are easy to
migrate once I have the whole structure in place.
Thanks a lot to all for replying to my post that quickly. Keep them coming
if you have more ideas
Regards,
Constantin
http://www.goodstockimages.com
From: Reece Hart <[EMAIL PROTECTED]>
To: MC Moisei <[EMAIL PROTECTED]>
CC: pgsql-general
Subject: Re: [GENERAL] Mapping/DB Migration tool
Date: Tue, 25 Jul 2006 14:49:34 -0700
On Tue, 2006-07-25 at 13:59 -0500, MC Moisei wrote:
> I'm looking to migrate psql db1 to a psql db2 that has a different
> structure
> even though 70% would be the same.
Depending on how much the structure changes (as opposed to more trivial
things like column names), you might consider whether you could actually
use the database itself to do this.
For some kinds of changes, and especially those that make destructive
in-place changes that might require debugging, I've written views which
generate the SQL statements to execute. I then do something like:
$ psql -Atc 'select sql from sql_changes' | psql -Xa
This works particularly well when the changes can be computed in some
way from the database, such as creating indexes for unindexed PKs
(postgresql doesn't require indexes on PKs).
You'd probably want to do this by making copies of the original database
as a template ('create database db2 template db1') or createdb -T .
-Reece
--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] Executing an SQL query from an internal function...
If I am implementing an internal function in PostgreSQL with the C programming language, can I use one of the C API's to execute a query from within the function? Is there another way to execute an SQL query from within an internal function implemented in C? Thanks, Scott Huey ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Mapping/DB Migration tool
On Tue, 2006-07-25 at 17:21 -0500, MC Moisei wrote:
The main thing is I changed a bunch of date types to timestamp type. Is
there a simple way to change the type on such fields ?
Yes, and more generally to change the type of a column. See below:
[EMAIL PROTECTED]> create table timely (quand date);
CREATE TABLE
Time: 14.385 ms
[EMAIL PROTECTED]> insert into timely values ('1968-11-22');
INSERT 0 1
Time: 2.398 ms
[EMAIL PROTECTED]> insert into timely values (now());
INSERT 0 1
Time: 4.683 ms
[EMAIL PROTECTED]> select * from timely ;
quand
1968-11-22
2006-07-25
(2 rows)
Time: 2.263 ms
[EMAIL PROTECTED]> alter table timely alter column quand type timestamp;
ALTER TABLE
Time: 39.002 ms
[EMAIL PROTECTED]> select * from timely ;
quand
-
1968-11-22 00:00:00
2006-07-25 00:00:00
(2 rows)
Time: 1.457 ms
Similarly, try 'alter table timely rename column quand to cuando' .
Also: \h alter table
--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
Re: [GENERAL] Executing an SQL query from an internal function...
Redefined Horizons wrote: > If I am implementing an internal function in PostgreSQL with the C > programming language, can I use one of the C API's to execute a query > from within the function? Is there another way to execute an SQL query > from within an internal function implemented in C? The SPI interface is designed to do that. http://www.postgresql.org/docs/8.1/static/spi.html -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: 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
[GENERAL] question about default value and timestamp
Hi list, i have a table with a column where the default value is current_timestamp, but somehow all the tuples (around 8.000.000) have the same timestamp, which is, honestly speaking, not what i intended to do. So is the current_timestamp function only executed when the insert statement starts? I mean of course i can change it and insert the current_timestamp explicitly in every tuple, i just would like to know why it doesn't work the way i wanted it to work... Thanks! Chris -- Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] question about default value and timestamp
am 26.07.2006, um 7:26:09 +0200 mailte Christian Rengstl folgendes: > Hi list, > > i have a table with a column where the default value is > current_timestamp, but somehow all the tuples (around 8.000.000) have > the same timestamp, which is, honestly speaking, not what i intended > to do. So is the current_timestamp function only executed when the > insert statement starts? I mean of course i can change it and insert No, but when the _Transaction_ starts. You can use timeofday() instead. HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
