<ghaverla (AT) freenet (DOT) edmonton.ab.ca> writes:
Great minds think alike ;-) --- I was just gonna suggest that.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

I know what I want to do has been done a thousand times before, however I am
looking for suggestions on the best way to go about it. I have two tables,
one holds products, the other holds photos. There is a one to many
relationship between products and photographs.
For example...

TABLE 1 (products):
products.id
products.title
products.description

TABLE 2 (product photos)
photos.id
photos.id_product
photos.filename
photos.height
photos.width
photos.position

the 'position' field is used to determine which is the main photo (photos
are ordered by their position). IN other words the photo with position 1
would be the main thumbnail.

There are two things I would like to be able to get from a SELECT statement.
The first is a list of products with the thumbnail info for the product. I
immediately see two ways of doing this but am looking for a better solution.

SOLUTION 1: select all products, and then loop through that result set and
do another select to get the photo information on each iteration of the
loop. This would be slow, and create unnecesary overhead on the database. I
do not think this is a good solution.

SOLUTION 2: select all products and then use subselects to get the
photograph information. I do not like this solution either, as there seems
to be no clear way to get multiple fields such as filename and height and
width etc without many subselects, or perhaps a creative concatenation
routine.

SOLUTION 3: I am open to suggestions... please!

The other main task I would like to accomplish is to be able to select all
the product information for a single product and get all the photographs for
the product as well. However, aside from using an array to return all photos
in that array in the select for products, I cannot think of an efficient way
to do this without issuing two queries (one to get the product, and one to
get the photo).

I am very interested to hear how others have tacked simular situations like
this. Any help is greatly appreciated.

Alan





---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Alan T. Miller wrote:
You basically want to JOIN the tables, I think.

This is from the top of my head in the early morning
without any coffee, so please excuse any stupid syntax
errors ...

SELECT pr.*, ph.* FROM products AS pr, photos AS ph
WHERE pr.id = ph.id_product and ph.position = 1;

That will give you one row for each product, along with
the photo information for the first thumbnail.

SELECT pr.*, ph.* FROM products AS pr, photos AS ph
WHERE pr.id = ph.id_product and pr.id == your_desired_product_id;

That will give you one row for each photo which belongs to
your_desired_product_id, as well as that product's data
(which the same for every row).

Please note that the may well be more efficient ways to
perform those joins (and I'm sure someone else will
mention them). But basically the above should work.

Best regards
Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"That's what I love about GUIs: They make simple tasks easier,
and complex tasks impossible."
-- John William Chambless

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Well, I captured a run using strace. 301 lines I believe, I've
edited it down to 120 or so. It's included below.

At about line 59, you can see where it opens the .pgpass file. It
opens a socket (to postmaster?) and after that it tries to control
the tty so that it can be put into noecho mode to manually
retrieve a password. After some other stuff, it again opens
..pgpass and talks to a socket, this time failing with the message
cron sends me, and the strace ends. I guess the password isn't
correct, although it does work for a shell as we seen earlier.
The line from .pgpass in the strace is listed as 34 characters
long, but only 32 characters are displayed. One character has
been chopped from the password, so that and the newline make 34
which is what it is supposed to be.

Gord
-----
Removed: brk(), mmap(), munmap(), select(), getrlimit(),
setrlimit(),
rt_sigaction(), rt_sigprocmask(), non-postgres system
library stuff
(ld.so, libc, etc. except pam, krb), _sysctl(), ioctl(),
duplicate
getpid(), gettimeofday(), duplicate stat64(), duplicate
open(),
Plaintext password changed to PASSWORD

execve("/usr/bin/psql", ["/usr/bin/psql", "-q", "-X", "-t", "-d",
"template1", "-P", "border=0", "-c", "
SELECT datname
FROM pg_database
WHERE datallowconn
ORDER BY datname"], [/* 11 vars */]) = 0
uname({sys="Linux", node="newmain.materia", ...}) = 0
vfork() = 17976
--- SIGCHLD (Child exited) @ 0 (0) ---
fstat64(3, {st_mode=S_IFIFO|0600, st_size=0, ...}) = 0
read(3, "PGLIB=/usr/lib/postgresql/lib\nPW"..., 4096) = 162
read(3, "", 4096) = 0
close(3) = 0
waitpid(17976, [{WIFEXITED(s) && WEXITSTATUS(s) == 0}], 0) = 17976
execve("/usr/lib/postgresql/bin/psql",
["/usr/lib/postgresql/bin/psql", "-q", "-X", "-t", "-P",
"border=0", "-c", "\nSELECT datname\n FROM "..., "-d",
"template1"], [/* 13 vars */]) = 0
uname({sys="Linux", node="newmain.materia", ...}) = 0
open("/usr/lib/libpq.so.3", O_RDONLY) = 3
read(3,
"\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0pX \0\000"..., 512)
= 512
fstat64(3, {st_mode=S_IFREG|0644, st_size=127184, ...}) = 0
close(3) = 0
open("/lib/libpam.so.0", O_RDONLY) = 3
read(3,
"\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0`\ 25\0\000"...,
512) = 512
fstat64(3, {st_mode=S_IFREG|0644, st_size=30360, ...}) = 0
close(3) = 0
open("/usr/lib/libkrb5.so.3", O_RDONLY) = 3
read(3,
"\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0p\ 372\0"..., 512)
= 512
fstat64(3, {st_mode=S_IFREG|0644, st_size=425800, ...}) = 0
close(3) = 0
open("/usr/lib/libk5crypto.so.3", O_RDONLY) = 3
read(3,
"\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\2 205\0"..., 512)
= 512
fstat64(3, {st_mode=S_IFREG|0644, st_size=138664, ...}) = 0
close(3) = 0
getpid() = 17973
getuid32() = 31 (postgres)
stat64("/etc/krb5.conf", {st_mode=S_IFREG|0644, st_size=2602,
....}) = 0
open("/etc/krb5.conf", O_RDONLY) = 3
access("/etc/krb5.conf", W_OK) = -1 EACCES (Permission
denied)
fstat64(3, {st_mode=S_IFREG|0644, st_size=2602, ...}) = 0
read(3, "[libdefaults]\n\tdefault_realm = M"..., 4096) = 2602
read(3, "", 4096) = 0
close(3) = 0
stat64("/usr/etc/krb5.conf", 0xbfffd46c) = -1 ENOENT (No such file
or directory)
open("/dev/urandom", O_RDONLY) = 3
fstat64(3, {st_mode=S_IFCHR|0444, st_rdev=makedev(1, 9), ...}) = 0
read(3,
"\271\254c\353\240\200\371$\310\377g\356lM\27\3316 \337\243"...,
20) = 20
close(3) = 0
open("/tmp/krb5cc_31", O_RDONLY) = -1 ENOENT (No such file
or directory)
geteuid32() = 31 (postgres)
socket(PF_FILE, SOCK_STREAM, 0) = 3
connect(3, {sa_family=AF_FILE, path="/var/run/.nscd_socket"}, 110)
= 0
writev(3, [{"\2\0\0\0\1\0\0\0\3\0\0\0", 12}, {"31\0", 3}], 2) = 15
read(3, "\2\0\0\0\1\0\0\0\t\0\0\0\2\0\0\0\37\0\0\0
\0\0\0\t\0\0"..., 36) = 36
read(3, "postgres\0x\0postgres\0/var/lib/pos"..., 46) = 46
close(3) = 0
stat64("/var/lib/postgres/.pgpass", {st_mode=S_IFREG|0600,
st_size=34, ...}) = 0
open("/var/lib/postgres/.pgpass", O_RDONLY|O_LARGEFILE) = 3
fstat64(3, {st_mode=S_IFREG|0600, st_size=34, ...}) = 0
read(3, "localhost:5432:*ostgres:PASSWORD"..., 4096) = 34
read(3, "", 4096) = 0
close(3) = 0
socket(PF_FILE, SOCK_STREAM, 0) = 3
fcntl64(3, F_SETFL, O_RDONLY|O_NONBLOCK) = 0
connect(3, {sa_family=AF_FILE,
path="/var/run/postgresql/.s.PGSQL.5432"}, 110) = 0
getsockopt(3, SOL_SOCKET, SO_ERROR, [0], [4]) = 0
getsockname(3, {sa_family=AF_FILE, path=@}, [2]) = 0
poll([{fd=3, events=POLLOUT|POLLERR, revents=POLLOUT}], 1, -1) = 1
send(3, "\0\0\0*\0\3\0\0user\0postgres\0database\0t".. ., 42, 0) =
42
poll([{fd=3, events=POLLIN|POLLERR, revents=POLLIN}], 1, -1) = 1
recv(3, "R\0\0\0\10\0\0\0\3", 16384, 0) = 9
close(3) = 0
open("/dev/tty", O_RDONLY|O_LARGEFILE) = -1 ENXIO (No such device
or address)
open("/dev/tty", O_WRONLY|O_CREAT|O_TRUNC|O_LARGEFILE, 0666) = -1
ENXIO (No such device or address)
ioctl(0, SNDCTL_TMR_TIMEBASE or TCGETS, 0xbffffaf0) = -1 EINVAL
(Invalid argument)
ioctl(0, SNDCTL_TMR_TIMEBASE or TCGETS, 0xbffffac0) = -1 EINVAL
(Invalid argument)
ioctl(0, SNDCTL_TMR_CONTINUE or TCSETSF, {B50 -opost isig icanon
-echo ...}) = -1 EINVAL (Invalid argument)
write(2, "Password: ", 10) = 10
fstat64(0, {st_mode=S_IFIFO|0600, st_size=0, ...}) = 0
read(0, "", 4096) = 0
ioctl(0, SNDCTL_TMR_TIMEBASE or TCGETS, 0xbffffac0) = -1 EINVAL
(Invalid argument)
ioctl(0, SNDCTL_TMR_CONTINUE or TCSETSF, {B50 -opost isig icanon
echo ...}) = -1 EINVAL (Invalid argument)
write(2, "\n", 1) = 1
getuid32() = 31 (postgres)
stat64("/etc/krb5.conf", {st_mode=S_IFREG|0644, st_size=2602,
....}) = 0
open("/etc/krb5.conf", O_RDONLY) = 3
access("/etc/krb5.conf", W_OK) = -1 EACCES (Permission
denied)
fstat64(3, {st_mode=S_IFREG|0644, st_size=2602, ...}) = 0
read(3, "[libdefaults]\n\tdefault_realm = M"..., 4096) = 2602
read(3, "", 4096) = 0
close(3) = 0
stat64("/usr/etc/krb5.conf", 0xbfffd46c) = -1 ENOENT (No such file
or directory)
open("/dev/urandom", O_RDONLY) = 3
fstat64(3, {st_mode=S_IFCHR|0444, st_rdev=makedev(1, 9), ...}) = 0
read(3,
"\3120\303\255\376\17\326\377\347\356\265\215\22\3 5\355"..., 20) =
20
close(3) = 0
open("/tmp/krb5cc_31", O_RDONLY) = -1 ENOENT (No such file
or directory)
open("/tmp/krb5cc_31", O_RDONLY) = -1 ENOENT (No such file
or directory)
geteuid32() = 31 (postgres)
socket(PF_FILE, SOCK_STREAM, 0) = 3
connect(3, {sa_family=AF_FILE, path="/var/run/.nscd_socket"}, 110)
= 0
writev(3, [{"\2\0\0\0\1\0\0\0\3\0\0\0", 12}, {"31\0", 3}], 2) = 15
read(3, "\2\0\0\0\1\0\0\0\t\0\0\0\2\0\0\0\37\0\0\0
\0\0\0\t\0\0"..., 36) = 36
read(3, "postgres\0x\0postgres\0/var/lib/pos"..., 46) = 46
close(3) = 0
stat64("/var/lib/postgres/.pgpass", {st_mode=S_IFREG|0600,
st_size=34, ...}) = 0
open("/var/lib/postgres/.pgpass", O_RDONLY|O_LARGEFILE) = 3
fstat64(3, {st_mode=S_IFREG|0600, st_size=34, ...}) = 0
read(3, "localhost:5432:*ostgres:PASSWORD"..., 4096) = 34
read(3, "", 4096) = 0
close(3) = 0
socket(PF_FILE, SOCK_STREAM, 0) = 3
fcntl64(3, F_SETFL, O_RDONLY|O_NONBLOCK) = 0
connect(3, {sa_family=AF_FILE,
path="/var/run/postgresql/.s.PGSQL.5432"}, 110) = 0
getsockopt(3, SOL_SOCKET, SO_ERROR, [0], [4]) = 0
getsockname(3, {sa_family=AF_FILE, path=@}, [2]) = 0
poll([{fd=3, events=POLLOUT|POLLERR, revents=POLLOUT}], 1, -1) = 1
send(3, "\0\0\0*\0\3\0\0user\0postgres\0database\0t".. ., 42, 0) =
42
poll([{fd=3, events=POLLIN|POLLERR, revents=POLLIN}], 1, -1) = 1
recv(3, "R\0\0\0\10\0\0\0\3", 16384, 0) = 9
write(2, "psql: fe_sendauth: no password s"..., 40) = 40
close(3) = 0
exit_group(2) = ?



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

<ghaverla (AT) freenet (DOT) edmonton.ab.ca> writes:
Hm. There is nothing apparently wrong in the trace (though it's too
bad we cannot see whether the password from the file is actually sent
in the subsequent connection-request packet ... does your strace have
an option to print more of the data involved in a send call?)

This part of the trace implies that you're using nscd for
authentication, which raises a few red flags for me since I've
seen bugs in nscd before. Can you try turning off nscd and see
if the behavior changes?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings