Thread: UPDATE Query problem
Folks, I have a database that contains a chronological journal of activity. Forvarious reasons, this journal contains both completeand incomplete records,and while all records are timestamped, the primary key is not strictly orderedby timestamp. What I want to do is update each incomplete record with the contents of thelast previous complete record. As a simple-mindedtest case: CREATE TABLE history AS ( history_id SERIAL PRIMARY KEY, period_date TIMESTAMP, fieldA VARCHAR(30), fieldB INT4 ); CREATE VIEW complete_history_records AS SELECT history.* FROM history WHERE fieldA IS NOT NULL and fieldB IS NOT NULL UPDATE history SET fieldA = chr.fieldA fieldB = chr.fieldB FROM (SELECT complete_history_records.* WHERE ??? ) chr WHERE (history.fieldA IS NULL or history.fieldB IS NULL); The problem is that I cannot figure out a subselect that will allow me toselect the last complete history record prior tothe one being updated. Itseems like I need to reference a field in the main query in the subselect,which can't be done. To further hamper things, for portability reasons, I can use neither SELECTDISTINCT ON nor custom functions. I'm stumped. Please offer suggestions! -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
"Josh Berkus" <josh@agliodbs.com> writes:
> The problem is that I cannot figure out a subselect that will allow me to
> select the last complete history record prior to the one being
> updated.
Sure you can. You can't alias history in the UPDATE, but you can alias
it in the subselect, so:
UPDATE history SET fieldA = (SELECT fieldA FROM history older WHERE older.key = history.key AND older.fieldA IS NOT
NULLAND older.fieldB IS NOT NULL AND older.timestamp = (SELECT max(timestamp) FROM history oldest WHERE
oldest.key= history.key AND oldest.fieldA IS NOT NULL AND oldest.fieldB IS NOT NULL)),
fieldB = (SELECT fieldB FROM ... repeat entire subselect above ...)
WHERE (history.fieldA IS NULL or history.fieldB IS NULL);
This will work and (AFAIK) is fully SQL-compliant, but it will be
slower than the dickens because of all those subselects :-(. Might
be tolerable if the key field is near-unique and is indexed, but
heaven help you if not.
> To further hamper things, for portability reasons, I can use neither SELECT
> DISTINCT ON nor custom functions.
Too bad. SELECT DISTINCT ON would let you get rid of the bottom SELECT
max() and would let you exploit an index on (key,timestamp). By the
time the query above finishes running, very likely you could talk your
boss into accepting a nonstandard solution ;-)
Also, just because PG can handle the above doesn't mean every RDBMS does
(do I need to name names?). What products do you really need it to
be portable to?
regards, tom lane
On Thu, 17 Jan 2002, Josh Berkus wrote: > Folks, > > I have a database that contains a chronological journal of activity. For > various reasons, this journal contains both complete and incomplete records, > and while all records are timestamped, the primary key is not strictly ordered > by timestamp. > > What I want to do is update each incomplete record with the contents of the > last previous complete record. As a simple-minded test case: > > CREATE TABLE history AS ( > history_id SERIAL PRIMARY KEY, > period_date TIMESTAMP, > fieldA VARCHAR(30), > fieldB INT4 ); > > CREATE VIEW complete_history_records AS > SELECT history.* > FROM history WHERE fieldA IS NOT NULL > and fieldB IS NOT NULL > > UPDATE history SET fieldA = chr.fieldA > fieldB = chr.fieldB > FROM (SELECT complete_history_records.* > WHERE ??? ) chr > WHERE (history.fieldA IS NULL or > history.fieldB IS NULL); > > The problem is that I cannot figure out a subselect that will allow me to > select the last complete history record prior to the one being updated. It > seems like I need to reference a field in the main query in the subselect, > which can't be done. > > To further hamper things, for portability reasons, I can use neither SELECT > DISTINCT ON nor custom functions. Isn't update...from already an extension? In any case, is performance really important? I think something like: update history set fieldA=chr.fieldA, fieldB=chr.fieldB from complete_history_records chr where (history.fieldA is null or history.fieldB is null) and chr.period_date=(select max(period_date) from complete_history_records where period_date<history.period_date); might work if really slow.
"Josh Berkus" <josh@agliodbs.com> writes:
> Interesting. however, it appears to give me the most recent record with
> non-NULL values. What I want is the most recent record with non-NULL values
> *before* the record I'm trying to update.
Oh, I'm sorry: forgot the extra qualification on the innermost SELECT:
AND oldest.timestamp < history.timestamp
> Yeah, you guessed it ... MS SQL Server 7.
I dunno, how good is SQL Server on subselects?
regards, tom lane
Tom, > Sure you can. You can't alias history in the UPDATE, but you can alias > it in the subselect, so: > > UPDATE history SET fieldA = > (SELECT fieldA FROM history older > WHERE older.key = history.key AND > older.fieldA IS NOT NULL AND older.fieldB IS NOT NULL AND > older.timestamp = > (SELECT max(timestamp) FROM history oldest > WHERE oldest.key = history.key AND > oldest.fieldA IS NOT NULL AND oldest.fieldB IS NOT NULL)), > fieldB = (SELECT fieldB FROM ... repeat entire subselect above ...) > WHERE (history.fieldA IS NULL or > history.fieldB IS NULL); Interesting. however, it appears to give me the most recent record withnon-NULL values. What I want is the most recentrecord with non-NULL values*before* the record I'm trying to update. In other words, if I have thefollowing data: history id timestamp fieldA fieldB 1341 6/30/00 KCKG 1 1345 7/31/00 KC 1 1402 8/31/00 NULL NULL 2799 9/30/00 NULL NULL 1581 10/31/00 KC 2 1673 11/30/00 KC 2 I want records 1402 and 2799 to be updated from record 1345, not from record1673. > This will work and (AFAIK) is fully SQL-compliant, but it will be > slower than the dickens because of all those subselects :-(. Might > be tolerable if the key field is near-unique and is indexed, but > heaven help you if not. The key field is unique. And slow is OK ... the history-correction programruns overnight. I just can't afford to take aprocedural approach and correctone record at a time ... there are 200,000 records and growing at a rate of8,000 recordsper month. > Also, just because PG can handle the above doesn't mean every RDBMS does > (do I need to name names?). What products do you really need it to > be portable to? Yeah, you guessed it ... MS SQL Server 7. Which kills custom functions orcustom aggregates, something that would make thiswhole process a lot easier. Thanks for the help! -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Tom, > Oh, I'm sorry: forgot the extra qualification on the innermost SELECT: > > AND oldest.timestamp < history.timestamp Hmmm ... I'll try both solutions tommorrow. That is, I'll see if they portacross databases ... > > Yeah, you guessed it ... MS SQL Server 7. > > I dunno, how good is SQL Server on subselects? Not very good. A lot of stuff, like subselects in the SELECT line, is notsupported. And MS has gotten further from theSQL standard with each updatesince SQL Server 7.0 ... -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
I try this code CREATE FUNCTION pltclu_call_handler() RETURNS OPAQUE AS '/usr/lib/postgresql/pltcl.so' LANGUAGE 'C'; and get this error ERROR: Load of file /usr/lib/postgresql/pltcl.so failed: /usr/lib/postgresql/pltcl.so: undefined symbol: pg_get_enconv_by_encoding anyone help me with this? murray hobbs ps here's my .configure command ./configure --enable-multibyte=UNICODE --enable-unicode-conversion --enable-locale --bindir=/usr/local/bin --libdir=/usr/lib--includedir=/usr/include --mandir=/usr/local/man --with-tcl --enable-odbc --with-unixodbc --enable-syslog
Murray Prior Hobbs <murray@efone.com> writes:
> ERROR: Load of file /usr/lib/postgresql/pltcl.so failed:
> /usr/lib/postgresql/pltcl.so: undefined symbol: pg_get_enconv_by_encoding
Looks like a multibyte-enabled pltcl and a non-multibyte-enabled
backend. Given that they were clearly built at different times and
with different configurations, one might also wonder if they're even
the same Postgres version.
regards, tom lane
you are so right bugga, and i thought i was being both so clever and so careful m Tom Lane wrote: >Murray Prior Hobbs <murray@efone.com> writes: > >>ERROR: Load of file /usr/lib/postgresql/pltcl.so failed: >>/usr/lib/postgresql/pltcl.so: undefined symbol: pg_get_enconv_by_encoding >> > >Looks like a multibyte-enabled pltcl and a non-multibyte-enabled >backend. Given that they were clearly built at different times and >with different configurations, one might also wonder if they're even >the same Postgres version. > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster >
Tom, Stephan, Well, you'll be interested to know that Stephan's solution worked for bothPostgreSQL and MS SQL Server ... as far as parsinggoes. On PostgreSQL, thequery took 14 minutes to complete. On MS SQL Server, it never completed at all. Looks like I will have to take asemi-procedural approach with MS SQL Serverafter all. Just another evidenceof the superiority of Postgres ... -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
<pre><br />after i have succesfully used createlan script to load both the trusted and untrusted tlc languages i try
sometests<br /><br /><br />i create a test function (right out of the docs)<br /><br /><br /> CREATE FUNCTION
tcl_max(int4, int4) RETURNS int4 AS ' if {$1 > $2} {return $1} return $2' LANGUAGE 'pltclu';
<br /><br />and i try to run this stest<br /><br /> select tcl_max(4,6);
<br />but i get<br /><br /><br /> ERROR: fmgr_info: function 17020: cache lookup failed<br /><br />so i create
trustedversion<br /><br /><br /> CREATE FUNCTION tcl_max (int4, int4) RETURNS int4 AS ' if {$1 > $2} {return
$1} return $2' LANGUAGE 'pltcl';<br /><br />and i again try to run this stest<br /><br /> select tcl_max(4,6);
<br />but i get instead<br /><br /> server closed the connection unexpectedly This probably means the server
terminatedabnormally before or while processing the request.connection to server was lost
<br />and if i look at the log<br /><br />postgres: murray kale [local] SELECT: error while loading shared libraries:
/usr/lib/postgresql/pltcl.so:undefined symbol: Tcl_CreateInterp<br />DEBUG: server process (pid 18415) exited with
exitcode 127
DEBUG: terminating any other active server processes
DEBUG: all server processes terminated; reinitializing shared memory and semaphores
DEBUG: database system was interrupted at 2002-01-19 15:01:29 EST
DEBUG: checkpoint record is at 0/4BAD10
DEBUG: redo record is at 0/4BAD10; undo record is at 0/0; shutdown TRUE
DEBUG: next transaction id: 2120; next oid: 49324
DEBUG: database system was not properly shut down; automatic recovery in progress
DEBUG: redo starts at 0/4BAD50
DEBUG: ReadRecord: record with zero length at 0/4C0FB4
DEBUG: redo done at 0/4C0F90
DEBUG: database system is ready<br /><br /><br />so what do i do now?<br /><br />log it as a bug?<br /><br /><br
/>murray<br/><br /><br /><br /></pre> so ok i go to the sources looking for test of pl/tlc or pl/tlcu (untrusted)<br
/><br/><br /><br /><br /><br /> Tom Lane wrote:<br /><blockquote cite="mid:6420.1011366281@sss.pgh.pa.us"
type="cite"><prewrap="">Murray Prior Hobbs <a class="moz-txt-link-rfc2396E"
href="mailto:murray@efone.com"><murray@efone.com></a>writes:<br /></pre><blockquote type="cite"><pre
wrap="">ERROR: Load of file /usr/lib/postgresql/pltcl.so failed: <br />/usr/lib/postgresql/pltcl.so: undefined symbol:
pg_get_enconv_by_encoding<br/></pre></blockquote><pre wrap=""><br />Looks like a multibyte-enabled pltcl and a
non-multibyte-enabled<br/>backend. Given that they were clearly built at different times and<br />with different
configurations,one might also wonder if they're even<br />the same Postgres version.<br /><br /> regards,
tomlane<br /><br />---------------------------(end of broadcast)---------------------------<br />TIP 4: Don't 'kill -9'
thepostmaster<br /><br /></pre></blockquote><br /><br />
Murray Prior Hobbs <murray@efone.com> writes: > postgres: murray kale [local] SELECT: error while loading shared libraries: /usr/lib/postgresql/pltcl.so: undefined symbol:Tcl_CreateInterp > DEBUG: server process (pid 18415) exited with exit code 127 Kinda looks like your dynamic loader doesn't know where to find libtcl.so (and thinks that the appropriate way to fail is a hard exit(), which is not my idea of friendly). > so what do i do now? > log it as a bug? It's not a Postgres bug; you need to twiddle your shlib configuration. But since you didn't mention your platform, I can't offer any pointers beyond http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/install-post.html#AEN11747 (note that this is talking about finding Postgres' libraries; alter to suit wherever libtcl lives). regards, tom lane
Thanks tom but i think there's more to it error while loading shared libraries: /usr/lib/postgresql/pltcl.so: undefined symbol: Tcl_CreateInterp as you can see it knows where the library is - what i think it's complaining about is the undefined symbol so i do a grep through the sources and find the call - the only call - but there's no function declaration in the sources i did follow your link and i had read the page before - i'm on RedHat 7.2 so should not have needed to do that - but i did anyway - it made no difference is there meant to be Tcl_CreateInterp anywhere in the sources? murray Tom Lane wrote: >Murray Prior Hobbs <murray@efone.com> writes: > >>postgres: murray kale [local] SELECT: error while loading shared libraries: /usr/lib/postgresql/pltcl.so: undefined symbol:Tcl_CreateInterp >>DEBUG: server process (pid 18415) exited with exit code 127 >> > >Kinda looks like your dynamic loader doesn't know where to find >libtcl.so (and thinks that the appropriate way to fail is a hard exit(), >which is not my idea of friendly). > >>so what do i do now? >> > >>log it as a bug? >> > >It's not a Postgres bug; you need to twiddle your shlib configuration. >But since you didn't mention your platform, I can't offer any pointers >beyond >http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/install-post.html#AEN11747 >(note that this is talking about finding Postgres' libraries; alter >to suit wherever libtcl lives). > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > >http://archives.postgresql.org >
[2002-01-19 18:58] Murray Prior Hobbs said: | | Thanks tom but i think there's more to it | | error while loading shared libraries: /usr/lib/postgresql/pltcl.so: | undefined symbol: Tcl_CreateInterp | | as you can see it knows where the library is - what i think it's | complaining about is the undefined symbol | | so i do a grep through the sources and find the call - the only call - | but there's no function declaration in the sources | | i did follow your link and i had read the page before - i'm on RedHat | 7.2 so should not have needed to do that - but i did anyway - it made no | difference | | is there meant to be Tcl_CreateInterp anywhere in the sources? No. This is provided by the tcl library: bash$ grep Tcl_CreateInter /usr/include/tcl8.3/tclDecls.h EXTERN Tcl_Interp * Tcl_CreateInterp _ANSI_ARGS_((void)); The problem is, as Tom said, that your tcl library is not being found by the system's linker. Try this: bash$ ldd /usr/lib/postgresql/pltcl.so I suspect you'll see a line containing "not found". brent -- "Develop your talent, man, and leave the world something. Records are really gifts from people. To think that an artist would love you enough to share his music with anyone is a beautiful thing." -- Duane Allman
i have had no trouble loading and using the pgpgsql language - and it lives in exactly the same place i've done as you suggested though - here is the output [murray@localhost dbSources]$ ldd /usr/lib/postgresql/pltcl.so libdl.so.2 => /lib/libdl.so.2 (0x40020000) libm.so.6=> /lib/i686/libm.so.6 (0x40024000) libc.so.6 => /lib/i686/libc.so.6 (0x40047000) /lib/ld-linux.so.2=> /lib/ld-linux.so.2 (0x80000000) murray Brent Verner wrote: > >No. This is provided by the tcl library: > > bash$ grep Tcl_CreateInter /usr/include/tcl8.3/tclDecls.h > EXTERN Tcl_Interp * Tcl_CreateInterp _ANSI_ARGS_((void)); > >The problem is, as Tom said, that your tcl library is not being >found by the system's linker. Try this: > > bash$ ldd /usr/lib/postgresql/pltcl.so > >I suspect you'll see a line containing "not found". > > brent > [2002-01-19 18:58] Murray Prior Hobbs said: | | Thanks tom but i think there's more to it | | error while loading shared libraries: /usr/lib/postgresql/pltcl.so: | undefined symbol: Tcl_CreateInterp | | as you can see it knows where the library is - what i think it's | complaining about is the undefined symbol | | so i do a grep through the sources and find the call - the only call - | but there's no function declaration in the sources | | i did follow your link and i had read the page before - i'm on RedHat | 7.2 so should not have needed to do that - but i did anyway - it made no | difference | | is there meant to be Tcl_CreateInterp anywhere in the sources?
maybe this is a dumb question but are not all the tcl sources part of the source distribution? like - am i to assume then that there are binaries in the distribution for which there is no code? like - if i go looking for somethng in the code should not i find it? murray Brent Verner wrote: > >No. This is provided by the tcl library: > > bash$ grep Tcl_CreateInter /usr/include/tcl8.3/tclDecls.h > EXTERN Tcl_Interp * Tcl_CreateInterp _ANSI_ARGS_((void)); > >The problem is, as Tom said, that your tcl library is not being >found by the system's linker. Try this: > > bash$ ldd /usr/lib/postgresql/pltcl.so > >I suspect you'll see a line containing "not found". > > brent > [2002-01-19 18:58] Murray Prior Hobbs said: | | Thanks tom but i think there's more to it | | error while loading shared libraries: /usr/lib/postgresql/pltcl.so: | undefined symbol: Tcl_CreateInterp | | as you can see it knows where the library is - what i think it's | complaining about is the undefined symbol | | so i do a grep through the sources and find the call - the only call - | but there's no function declaration in the sources | | i did follow your link and i had read the page before - i'm on RedHat | 7.2 so should not have needed to do that - but i did anyway - it made no | difference | | is there meant to be Tcl_CreateInterp anywhere in the sources?
Murray Prior Hobbs <murray@efone.com> writes:
> maybe this is a dumb question
> but are not all the tcl sources part of the source distribution?
I'm only going to say this one more time: Tcl is not part of Postgres.
pltcl depends on libtcl (note difference), and the loader is evidently
not finding libtcl.so. Which will absolutely NOT be in
/usr/lib/postgresql. The question for you is where it actually lives
(if it's installed at all), and next why the dynamic loader search path
isn't finding it.
regards, tom lane
<br /> ok ok - so i went off and read a LOT about Tcl - cool what a nice tool<br /><br /> and then i downloaded the
tcl/tksources and built with appropriate configure options and installed it - in approprate places (in /usr/bin - over
theold copies that were already there) and ran the tests and dowloaded some TCl samples and played with the apps <br
/><br/> as i had been using the postgres 7.2 beta i reinstalled 7.13 over the top - right from the start, reconfigured,
recompiled,reinstalled, reinitialised<br /><br /> and tried to call a tcl function yet again - but now i get this
error<br/><br /><pre>ERROR: pltcl: internal error - cannot create 'normal' interpreter</pre> but hmm, that's further
thani got before and at least the database does not restart itself in the process<br /><br /> and in the code i have
gotone step further<br /><br /> any clues?<br /><br /> murray<br /><pre> if ((pltcl_hold_interp =
Tcl_CreateInterp())== NULL)<br /> {<br /> elog(ERROR, "pltcl: internal error - cannot create
'hold'"<br /> "interpreter");<br /> }<br /><br />
/************************************************************<br/> * Create the two interpreters<br />
************************************************************/<br/> if ((pltcl_norm_interp =<br />
Tcl_CreateSlave(pltcl_hold_interp, "norm", 0)) == NULL)<br /> {<br /> elog(ERROR,<br />
"<b>pltcl: internal error - cannot create 'normal' interpreter</b>");<br /> }<br /><br /><br /><br
/></pre><br/><br /><br /><br /> murray<br /><br /><br /><br /><br /><br /><br /> Tom Lane wrote:<br /><blockquote
cite="mid:14131.1011461791@sss.pgh.pa.us"type="cite"><pre wrap="">Murray Prior Hobbs <a class="moz-txt-link-rfc2396E"
href="mailto:murray@efone.com"><murray@efone.com></a>writes:<br /></pre><blockquote type="cite"><pre
wrap="">maybethis is a dumb question<br />but are not all the tcl sources part of the source distribution?<br
/></pre></blockquote><prewrap=""><br />I'm only going to say this one more time: Tcl is not part of Postgres.<br /><br
/>pltcldepends on libtcl (note difference), and the loader is evidently<br />not finding libtcl.so. Which will
absolutelyNOT be in<br />/usr/lib/postgresql. The question for you is where it actually lives<br />(if it's installed
atall), and next why the dynamic loader search path<br />isn't finding it.<br /><br /> regards, tom lane<br
/><br/>---------------------------(end of broadcast)---------------------------<br />TIP 5: Have you checked our
extensiveFAQ?<br /><br /><a class="moz-txt-link-freetext"
href="http://www.postgresql.org/users-lounge/docs/faq.html">http://www.postgresql.org/users-lounge/docs/faq.html</a><br
/><br/></pre></blockquote><br /><br />