UPDATE Query problem

"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 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);

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.

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 with
non-NULL values. What I want is the most recent record with non-NULL values
*before* the record I'm trying to update. In other words, if I have the
following 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 record
1673.

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 program
runs overnight. I just can't afford to take a procedural approach and correct
one record at a time ... there are 200,000 records and growing at a rate of
8,000 records per 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 or
custom aggregates, something that would make this whole process a lot easier.

Thanks for the help!

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology 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:

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,

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 port
across 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 not
supported. And MS has gotten further from the SQL standard with each update
since SQL Server 7.0 ...

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology 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:

Tom, Stephan,

Well, you'll be interested to know that Stephan's solution worked for both
PostgreSQL and MS SQL Server ... as far as parsing goes. On PostgreSQL, the
query took 14 minutes to complete.

On MS SQL Server, it never completed at all. Looks like I will have to take a
semi-procedural approach with MS SQL Server after all. Just another evidence
of the superiority of Postgres ...

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco

after i have succesfully used createlan script to load both the trusted and untrusted tlc languages i try some tests

i create a test function (right out of the docs)

CREATE FUNCTION tcl_max (int4, int4) RETURNS int4 AS '
if {$1 > $2} {return $1}
return $2
' LANGUAGE 'pltclu';

and i try to run this stest

select tcl_max(4,6);

but i get

ERROR: fmgr_info: function 17020: cache lookup failed

so i create trusted version

CREATE FUNCTION tcl_max (int4, int4) RETURNS int4 AS '
if {$1 > $2} {return $1}
return $2
' LANGUAGE 'pltcl';

and i again try to run this stest

select tcl_max(4,6);

but i get instead

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost

and if i look at the log

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
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

so what do i do now?

log it as a bug?

murray

so ok i go to the sources looking for test of pl/tlc or pl/tlcu (untrusted)

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

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:

[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

ok ok - so i went off and read a LOT about Tcl - cool what a nice tool

and then i downloaded the tcl/tk sources and built with appropriate
configure options and installed it - in approprate places (in /usr/bin -
over the old copies that were already there) and ran the tests and
dowloaded some TCl samples and played with the apps

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

and tried to call a tcl function yet again - but now i get this error

ERROR: pltcl: internal error - cannot create 'normal' interpreter

but hmm, that's further than i got before and at least the database does
not restart itself in the process

and in the code i have got one step further

any clues?

murray

if ((pltcl_hold_interp = Tcl_CreateInterp()) == NULL)
{
elog(ERROR, "pltcl: internal error - cannot create 'hold' "
"interpreter");
}

/************************************************************
* Create the two interpreters
************************************************************/
if ((pltcl_norm_interp =
Tcl_CreateSlave(pltcl_hold_interp, "norm", 0)) == NULL)
{
elog(ERROR,
"*pltcl: internal error - cannot create 'normal' interpreter*");
}

murray

Tom Lane wrote:

[2002-01-19 19:40] Murray Prior Hobbs said:
|
| 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

Indeed. I just got finished installing a chroot image of
redhat-7.2 to test this. I am seeing the same Tcl_CreateInterp
problem you mentioned earlier. The pltcl language does not work
even from the 7.2b3 rpms. Can someone verify that pltcl works on
their stock redhat 7.2 system?

Are there a known bugs in the stock 7.2 binutils or any other part
of the toolchain that might be causing this problem? Most notably
is the absence of pltcl.so being linked to libtcl.so. Could this
be a problem with redhat's tcl package?

Monty, are you by chance running in a chroot?

confounded,
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

[2002-01-20 23:24] Murray Prior Hobbs said:
| Brent Verner wrote:
|
| >[2002-01-19 19:40] Murray Prior Hobbs said:
| >|
| >| 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
| >
| >Indeed. I just got finished installing a chroot image of
| >redhat-7.2 to test this. I am seeing the same Tcl_CreateInterp
| >problem you mentioned earlier. The pltcl language does not work
| >even from the 7.2b3 rpms. Can someone verify that pltcl works on
| >their stock redhat 7.2 system?
| >
| >Are there a known bugs in the stock 7.2 binutils or any other part
| >of the toolchain that might be causing this problem? Most notably
| >is the absence of pltcl.so being linked to libtcl.so. Could this
| >be a problem with redhat's tcl package?
| >
| >Monty, are you by chance running in a chroot?
| >
| if you mean me (Murray) nope - it's a bog standard RedHat 7.2 install

sorry! I know a guy named "Monty Hobbs"... I'm really too tired ;-)

| but i have installed Tcl from the sources from scratch - 8.3.4

Indeed I've tracked the problem down to the line that links
the pltcl.so library:

make[3]: Entering directory `/usr/local/cvs/pgsql/src/pl/tcl'
/bin/sh mkMakefile.tcldefs.sh '/usr/lib/tclConfig.sh' 'Makefile.tcldefs'
make[3]: Leaving directory `/usr/local/cvs/pgsql/src/pl/tcl'
make[3]: Entering directory `/usr/local/cvs/pgsql/src/pl/tcl'
gcc -pipe -O -D__NO_STRING_INLINES -D__NO_MATH_INLINES -fPIC -I../../../src/include -DHAVE_UNISTD_H=1 -DHAVE_LIMITS_H=1 -DHAVE_GETCWD=1 -DHAVE_OPENDIR=1 -DHAVE_STRSTR=1 -DHAVE_STRTOL=1 -DHAVE_TMPNAM=1 -DHAVE_WAITPID=1 -DHAVE_UNISTD_H=1 -DHAVE_SYS_PARAM_H=1 -DUSE_TERMIOS=1 -DHAVE_SYS_TIME_H=1 -DTIME_WITH_SYS_TIME=1 -DHAVE_TM_ZONE=1 -DHAVE_TM_GMTOFF=1 -DHAVE_TIMEZONE_VAR=1 -DHAVE_ST_BLKSIZE=1 -DSTDC_HEADERS=1 -DNEED_MATHERR=1 -DHAVE_SIGNED_CHAR=1 -DHAVE_SYS_IOCTL_H=1 -c -o pltcl.o pltcl.c
gcc -pipe -shared -Wl,-soname,libtcl.so.0 -o pltcl.so pltcl.o -L/usr/lib -ltcl -ldl -lieee -lm -lc
^^^^^^^^^^^

IIRC, this was changed to workaround another problem with the
tcl client library having name conflicts. This value (TCL_SHLIB_LD)
comes directly from the /usr/lib/tclConfig.sh file supplied by the
rpm. You can add the following line to src/pl/tcl/Makefile
below "-include Makefile.tcldefs"

TCL_SHLIB_LD = gcc -shared

to override the erronious value supplied by the system's tclConfig.sh.

| but just because i'm ignorant of many things - how would i check if i
| was running in chroot environment?

not sure. I always know when I am, because I setup the chroot.
Some web hosts will give you a chroot as well, but if you are
developing on your own workstation, there is little chance of
you being in a chroot and not knowing it.

hth.
b

--
"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