[SQL] How do you detect row version changes in PostgreSQL?
Hi all, Some databases have a hidden field that contains a row version key. Everytime an update is performed on a row the value of the version field changes. Does PostgreSQL have a similar hidden field? If not then I guess using a before update trigger that modifies a user defined field will provide an alternative solution. Greetings, Craig Manley.
[SQL] Re: Argument variables for select
On Mon, 28 Aug 2000, Yury Don wrote: > > Create Function VarSelect ( varchar, varchar ) > >returns int > >As ' > > Declare num int ; > > > > Begin > >Select Into num Count(*) From $1 Where $2 ; > >return num; > > End ; > >' language 'plpgsql' ; > > > > Could someone please explain who to type the exact syntax so that > > I can ship the table to select from as $1 and the condition to select > > what as $2? Or do I have to concatenate a string with the whole > > select statement and how to call this string? In MS SQL server this > > could be done with > >Exec ( query ) > > > AFAIK it's impossible with plpgsql, but it's possible in pltcl. It is really hard to believe that I'm in so very deep trouble with PostgreSQL. It seems to me that stored procedures are far from beeing as usefull as I learned them to know in MS SQL server. Once I try to follow one hint I'm standing in frot of the next even harder problem. web=# create function testfunc( ) web-# returns int web-# As ' web'# spi_exec "SELECT count(*) AS $num FROM testtable" web'# web'# return $num ; web'# End; ' web-# language 'pltcl' ; ERROR: Unrecognized language specified in a CREATE FUNCTION: 'pltcl'. Recognized languages are sql, C, internal and the created procedural languages. I have installed the pgtcl package of my Debian distribution, so I guess it should be available. Once more the question: Is it really necessary to use a further interpreter instead of sticking with SQL commands to use the original problem. I have to admit that my boss wonders why I'm switching from a working solution (MS SQL) to so much trouble :-(. I really hope to convince him to OpenSource but it's much harder than I expected. Kind regards Andreas.
[SQL] performance on insert/update
I have a big problem of performance, please help me. it is my work : first : COPY table1 FROM 'file'-> 43s, INSERT INTO table2 -> 34s, UPDATE table2 -> 1mn 29s ( =2m 46s : OK) second : COPY table1 FROM 'same file' -> 1m 10s, INSERT INTO table2-> 2m 14s, UPDATE table2 -> 5mn 20s ( =8m 44s ) third : COPY table1 FROM 'same file' -> 1m, INSERT INTO table2-> 2m 20s, UPDATE table2 -> 7mn 30s ( =10m 50s ) ... EXPLAIN UPDATE table2 (first, second and third) : Merge Join (cost=0.00..8425.71 rows=1 width=102) -> Index Scan using index2 on table2(cost=0.00..8320.70 rows=1000 width=42) SubPlan -> Index Scan using table1_pkey on table1 (cost=0.00..8.26 rows=1 width=46) -> Index Scan using index2 on table2 (cost=0.00..60.00 rows=1000 width=60) SubPlan -> Index Scan using index2 on table2 (cost=0.00..10.28 rows=1 width=42) SubPlan -> Aggregate (cost=8.26..8.26 rows=1 width=4) -> Index Scan using table1_pkey on table1 (cost=0.00..8.26 rows=1 width=4) -> Index Scan using table1_pkey on table1 (cost=0.00..8.26 rows=1 width=46) -> Index Scan using index2 on table2 (cost=0.00..10.28 rows=1 width=42) SubPlan -> Aggregate (cost=8.26..8.26 rows=1 width=4) -> Index Scan using table1_pkey on table1 (cost=0.00..8.26 rows=1 width=4) -> Index Scan using table1_pkey on table1 (cost=0.00..8.26 rows=1 width=46) -- .../bin/postmaster -d2 -D .../data -p 19118 -i -N64 -B1024 CREATE TABLE table1 (f1 VARCHAR(20) NOT NULL, date_conn DATETIME NOT NULL, time INT4, PRIMARY KEY(f1, date_conn)); CREATE INDEX index1 ON table1( f1, date_conn ) ; CREATE TABLE table2 (f1 VARCHAR(20),nb INT4,time INT4,tmc INT4,date DATE); CREATE INDEX index2 ON table2( f1, date ) ; CREATE VIEW view1 AS SELECT f1, ( SELECT COUNT(*) FROM table1 WHERE table1.f1=table2.f1 AND table1.date_conn::date=table2.date ) AS nb, ( SELECT SUM(table1.time) FROM table1 WHERE table1.f1=table2.f1 AND table1.date_conn::date=table2.date ) AS time, date FROM table2 WHERE exists ( SELECT f1, date_conn FROM table1 WHERE table1.f1=table2.f1 AND table1.date_conn::date=table2.date) ; CREATE INDEX index_view1 ON view1( f1, date ) ; -- I have a file 'file' : 20162 tuples. (f1/date_conn/time) "COPY table_brut FROM 'file' USING DELIMITERS '/'; INSERT INTO table2 SELECT DISTINCT f1, 0, 0, 0, date_conn::date FROM table1 WHERE not exists ( SELECT table2.f1, table2.date FROM table2, table1 WHERE table2.f1=table1.f1 AND table2.date=table1.date_conn::date ); UPDATE table2 SET nb=nb+( SELECT nb FROM view1 WHERE view1.f1=table2.f1 AND view1.date=table2.date ), temps=time+( SELECT time FROM view1 WHERE view1.f1=table2.f1 AND view1.date=table2.date ) WHERE table2.f1=view1.f1 AND table2.date=view1.date ; UPDATE table2 SET tmc=time/nb; DELETE FROM table1; -- Sorry for my english, and Thanks in advance. Jerome.
Re: [SQL] Re: Argument variables for select
Hi Andreas, I've worked with MS SQL stored procedures before and they are quite powerful. Its a shame postgres doesn't have the same level of features as offered by MS SQL, but apart from this area it is still a very good database. Perhaps in the coming versions we will see more stored procedure features? I'm not sure what your application is exactly but there are ways to work around the features that are missing. Good luck with convincing your boss :) Keith. At 02:28 PM 29/08/2000 +0200, Andreas Tille wrote: >On Mon, 28 Aug 2000, Yury Don wrote: > > > > Create Function VarSelect ( varchar, varchar ) > > >returns int > > >As ' > > > Declare num int ; > > > > > > Begin > > >Select Into num Count(*) From $1 Where $2 ; > > >return num; > > > End ; > > >' language 'plpgsql' ; > > > > > > Could someone please explain who to type the exact syntax so that > > > I can ship the table to select from as $1 and the condition to select > > > what as $2? Or do I have to concatenate a string with the whole > > > select statement and how to call this string? In MS SQL server this > > > could be done with > > >Exec ( query ) > > > > > AFAIK it's impossible with plpgsql, but it's possible in pltcl. > >It is really hard to believe that I'm in so very deep trouble with >PostgreSQL. It seems to me that stored procedures are far from beeing >as usefull as I learned them to know in MS SQL server. Once I try >to follow one hint I'm standing in frot of the next even harder problem. > > >web=# create function testfunc( ) >web-# returns int >web-# As ' >web'# spi_exec "SELECT count(*) AS $num FROM testtable" >web'# >web'# return $num ; >web'# End; ' >web-# language 'pltcl' ; >ERROR: Unrecognized language specified in a CREATE FUNCTION: >'pltcl'. Recognized languages are sql, C, internal and the created >procedural languages. > > >I have installed the pgtcl package of my Debian distribution, so I guess >it should be available. Once more the question: Is it really necessary to >use a further interpreter instead of sticking with SQL commands to use >the original problem. > >I have to admit that my boss wonders why I'm switching from a working >solution (MS SQL) to so much trouble :-(. I really hope to convince him >to OpenSource but it's much harder than I expected. > >Kind regards > > Andreas.
Re: [SQL] Re: Argument variables for select
Keith Wong wrote: > > Hi Andreas, > > I've worked with MS SQL stored procedures before and they are quite > powerful. Its a shame postgres doesn't have the same > level of features as offered by MS SQL, MS SQL is based on source code of Sybase v5. MS bought this source code to sybase. Sybase has stored procedure features that even Oracle v7 (and probably v8) doesn't have, as returning one ore more seult set(s).
Re: [SQL] How do you detect row version changes in PostgreSQL?
"Craig Manley" <[EMAIL PROTECTED]> writes: > Some databases have a hidden field that contains a row version key. > Everytime an update is performed on a row the value of the version field > changes. Does PostgreSQL have a similar hidden field? See xmin (and also cmin if you need to keep track of multiple changes intra-transaction). It might not be defined quite the way you want, but if you're not picky about what a "version number" is, it'll do. regards, tom lane
Re: [SQL] performance on insert/update
Jerome Raupach <[EMAIL PROTECTED]> writes: > I have a big problem of performance, please help me. You could code the update so it only evaluates the view once, rather than twice per table2 row as you now have it: UPDATE table2 SET nb=table2.nb+view1.nb, time=table2.time+view1.time FROM view1 WHERE view1.f1=table2.f1 and view1.date=table2.date; Subselects are nice, but they're not cheap. Joins are faster. The view itself also seems to be pretty inefficiently coded, although I'm not sure you can do much better given that GROUP BY doesn't really work in views right now. Hopefully by 7.2 there will be a better way to do the view. regards, tom lane
Re: [SQL] Re: Argument variables for select
I think you have to run "createlang pltcl db-foo" from the command line. As for your boss, here are three things I can think of off the top of my head: 1. All new products take some time to learn. If you can't use Postgres perfectly in a week or so, remember that you probably can't learn to be an Oracle or MS-SQL DBA in a week or so either. 2. Postgres is FREE. NO MONEY. 3. Postgres integrates very easily into a Unix environment. 4. Proprietary software is going the way of the Do-Do. Well that's four. At my job, we are building a database in Postgres to replace an MS-SQL thing. My version takes 12 hours to upload a months worth of data. You think that is bad--the SQL Server took 15 DAYS. Good luck, and sorry for the rant. W --- Andreas Tille <[EMAIL PROTECTED]> wrote: > On Mon, 28 Aug 2000, Yury Don wrote: > > > > Create Function VarSelect ( varchar, varchar ) > > >returns int > > >As ' > > > Declare num int ; > > > > > > Begin > > >Select Into num Count(*) From $1 Where $2 > ; > > >return num; > > > End ; > > >' language 'plpgsql' ; > > > > > > Could someone please explain who to type the > exact syntax so that > > > I can ship the table to select from as $1 and > the condition to select > > > what as $2? Or do I have to concatenate a > string with the whole > > > select statement and how to call this string? > In MS SQL server this > > > could be done with > > >Exec ( query ) > > > > > AFAIK it's impossible with plpgsql, but it's > possible in pltcl. > > It is really hard to believe that I'm in so very > deep trouble with > PostgreSQL. It seems to me that stored procedures > are far from beeing > as usefull as I learned them to know in MS SQL > server. Once I try > to follow one hint I'm standing in frot of the next > even harder problem. > > > web=# create function testfunc( ) > web-# returns int > web-# As ' > web'# spi_exec "SELECT count(*) AS $num FROM > testtable" > web'# > web'# return $num ; > web'# End; ' > web-# language 'pltcl' ; > ERROR: Unrecognized language specified in a CREATE > FUNCTION: 'pltcl'. Recognized languages are sql, C, > internal and the created procedural languages. > > > I have installed the pgtcl package of my Debian > distribution, so I guess > it should be available. Once more the question: Is > it really necessary to > use a further interpreter instead of sticking with > SQL commands to use > the original problem. > > I have to admit that my boss wonders why I'm > switching from a working > solution (MS SQL) to so much trouble :-(. I really > hope to convince him > to OpenSource but it's much harder than I expected. > > Kind regards > > Andreas. > __ Do You Yahoo!? Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/
[SQL] Viewing a function
How can a view a function after it's created ? I've created a SQL function that I need to review and possibly change. What is the best way to go about this. TIA
Fw: [SQL] Viewing a function
-Original Message- From: Stuart Foster <[EMAIL PROTECTED]> To: PG-SQL <[EMAIL PROTECTED]> Date: Wednesday, 30 August 2000 2:25 Subject: [SQL] Viewing a function Helllo Stuart, Good question. I have been fiddly with a function editor using zeos controls and I have lots of little problems, so that I have given up for a while. I successfully parsed function information. (The following is from memory and I dont have postgres with me right now). The body of the function is in pg_lang.prosrc (I think) or something like that (I found out most stuff by looking in the c code for psql. I work in delphi) You can work out param numbers from another field and work out the field types (I confess Icheated here. You can get field type out of system tables but I never actually worked out how). I could reconstruct a nice looking create function string using data in the data base. When I tried it execute things from windows things went wrong but it work in psql. I ended up just using it as a utility to create separated create function files wich I then loaded server side. Anyway... then problems. I dont think I ever had a successful update from client side. Once I committed a change I often could not successfully drop and create a function and vacuum would fail. To recover I had to drop all functions and re-create them. Using PERFORM seemed to cause particular problems. (Think that anything that is called by perform must be compiled in before anything that calls it. Order of creation seems to important. A full dump and create would fix these sort of problems) The cr/lf thing also gave problems although I wrote what I thought was a client side fix for this. You can have my source in Delphi (such as it is) if you are interested. I think there must be flags or dependencies with function managemnt which I dont understand. PS I have not had success editing function in pgaccess either (similar problems) A simple way to view functions is pg_dump -c -s database to just dump schema and drop statements. Cut and paste function definitions. The difficulty of function editing has retarded my putting tasks server-side which belong there. >How can a view a function after it's created ? >I've created a SQL function that I need to review and possibly change. What >is the best way to go about this. > > >TIA > Another stuart
[SQL] Problems with complex queries ...
Hey everybody !!! I am new on this list !!! I have a little problem . I try this on my system: (Postgres 6.5.2, Linux) "select n_lote from pedidos except select rp.n_lote from relpedidos rp, relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having sum(rp.cantidad)=sum(rf.cantidad)" and I get this result: ERROR: rewrite: comparision of 2 aggregate columns not supported but if I try this one: "select rp.n_lote from relpedidos rp, relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having sum(rp.cantidad)=sum(rf.cantidad)" It's OK !! What's up??? Do you think i found a bug ??? Do exists some limitation like this in subqueries?? (Perhaps Postgres don't accept using aggregates in subqueries ???) I tried this too: "select n_lote from pedidos where n_lote not in (select rp.n_lote from relpedidos rp, relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having sum(rp.cantidad)=sum(rf.cantidad))" but the result was the same ! And i get the same error message (or similar) when i try other variations. Thanks !!! Fer -- * ** ** ** * **** * * ** ** ** * *** * * * * *** * *** * ** *** * * * * * ** ** * * * (*) SymeX ==> http://www.lantik.com (*) Web en http://www.arrakis.es/~txino (*) Informate sobre LINUX en http://www.linux.org
Re: [SQL] Problems with complex queries ...
"J. Fernando Moyano" <[EMAIL PROTECTED]> writes: > I try this on my system: (Postgres 6.5.2, Linux) > "select n_lote from pedidos except select rp.n_lote from relpedidos rp, > relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having > sum(rp.cantidad)=sum(rf.cantidad)" > and I get this result: > ERROR: rewrite: comparision of 2 aggregate columns not supported I think this is fixed in 7.0. regards, tom lane
Re: [SQL] Problems with complex queries ...
Without seeing the schema or anything, a similar query to your first one appears to run on my Postgres 7.0.2 setup. It's probably worth upgrading. On Wed, 30 Aug 2000, J. Fernando Moyano wrote: > I try this on my system: (Postgres 6.5.2, Linux) > > "select n_lote from pedidos except select rp.n_lote from relpedidos rp, > relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having > sum(rp.cantidad)=sum(rf.cantidad)" > > and I get this result: > > ERROR: rewrite: comparision of 2 aggregate > columns not supported > > but if I try this one: > > "select rp.n_lote from relpedidos rp, relfacturas rf where > rp.n_lote=rf.n_lote group by rp.n_lote having sum(rp.cantidad)=sum(rf.cantidad)" > > It's OK !! > > What's up??? > Do you think i found a bug ??? > Do exists some limitation like this in subqueries?? > > (Perhaps Postgres don't accept using aggregates in subqueries ???) > > I tried this too: > > "select n_lote from pedidos where n_lote not in (select rp.n_lote from > relpedidos rp, relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote > having sum(rp.cantidad)=sum(rf.cantidad))" > > but the result was the same ! > > And i get the same error message (or similar) when i try other variations.
[SQL] Optimizing huge inserts/copy's
Hi all, Does anybody have any thoughts on optimizing a huge insert, involving something like 3 million records all at once? Should I drop my indices before doing the copy, and then create them after? I keep a tab-delimited file as a buffer, copy it, then do it again about 400 times. Each separate buffer is a few thousand records. We do this at night, so it's not the end of the world if it takes 8 hours, but I would be very grateful for some good ideas... Thanks W __ Do You Yahoo!? Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/
[SQL] Create Primary Key?
Apropos of my last question: Is there syntax to create a primary key after the table has been defined and populated? I think I could speed things up quite a bit by not having any indexes at all when I do my mass copies. Thanks, and my apologies if that is a totally stupid question. W __ Do You Yahoo!? Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/
Re: [SQL] Create Primary Key?
We don't currently support the SQL syntax for adding a PK to a table. However, if you have the columns as NOT NULL already, adding a unique index to the columns in question has the same general effect. Stephan Szabo [EMAIL PROTECTED] On Tue, 29 Aug 2000, Webb Sprague wrote: > Apropos of my last question: > > Is there syntax to create a primary key after the > table has been defined and populated? I think I could > speed things up quite a bit by not having any indexes > at all when I do my mass copies.
Re: [SQL] Optimizing huge inserts/copy's
Hi, there, 1. use copy ... from '.'; 2. write a PL/pgSQL function and pass multiple records as an array. However, if your table have a foreign key constraint, it cannot be speed up, I have same question as you, my table invloving 9-13 million rows, I don't know how can I add a foreign key them also? Webb Sprague wrote: > Hi all, > > Does anybody have any thoughts on optimizing a huge > insert, involving something like 3 million records all > at once? Should I drop my indices before doing the > copy, and then create them after? I keep a > tab-delimited file as a buffer, copy it, then do it > again about 400 times. Each separate buffer is a few > thousand records. > > We do this at night, so it's not the end of the world > if it takes 8 hours, but I would be very grateful for > some good ideas... > > Thanks > W > > __ > Do You Yahoo!? > Yahoo! Mail - Free email you can access from anywhere! > http://mail.yahoo.com/ -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Create Primary Key?
Hi, It suppose to use ALTER TABLE tablename ADD constraint xxx primary key(columnname), unforturatly, Pg7.0 still haven't implemented it yet, so except foreign key ,other constarints, you have to choose: 1. rename your old table, recreate an new one with primary key, then use insert into newtable select * from oldtable. 2. use pg_dump -t tablename -f scriptfile dbname in shell , edit that scriptfile, add primary key there, drop your table, then reload it again: psql dbname Apropos of my last question: > > Is there syntax to create a primary key after the > table has been defined and populated? I think I could > speed things up quite a bit by not having any indexes > at all when I do my mass copies. > > Thanks, and my apologies if that is a totally stupid > question. > > W > > __ > Do You Yahoo!? > Yahoo! Mail - Free email you can access from anywhere! > http://mail.yahoo.com/ -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Optimizing huge inserts/copy's
On Tue, 29 Aug 2000, Jie Liang wrote: > Hi, there, > > 1. use copy ... from '.'; > 2. write a PL/pgSQL function and pass multiple records as an array. > > However, if your table have a foreign key constraint, it cannot be speed > up, > > I have same question as you, my table invloving 9-13 million rows, I > don't > know how can I add a foreign key them also? I haven't tried it on really large tables, but does it turn out faster to use ALTER TABLE ADD CONSTRAINT to add the foreign key constraint after the data is loaded and the indexes are created?
Re: [SQL] Create Primary Key + Massive Copy's?
This is my next approach--I got rid of all indexes and PK's, and then created an index with unique after everything is added. Wish me luck. W --- Stephan Szabo <[EMAIL PROTECTED]> wrote: > > We don't currently support the SQL syntax for adding > a PK to a table. However, if you have the columns > as NOT NULL already, adding a unique index to the > columns in question has the same general effect. > > Stephan Szabo > [EMAIL PROTECTED] > > On Tue, 29 Aug 2000, Webb Sprague wrote: > > > Apropos of my last question: > > > > Is there syntax to create a primary key after the > > table has been defined and populated? I think I > could > > speed things up quite a bit by not having any > indexes > > at all when I do my mass copies. > __ Do You Yahoo!? Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/
