Thread: left outer join on multi tables
Hi, here are my tables
table name
{
id integer primary key
first_name text,
middle_name text,
last_name text
}
table address
{
id integer primary key
number int,
street text,
city text,
state text
}
table work
{
id integer primary key
hours text,
shift
}
table person
{
id integer primary key
namid integer references name(id),
addressid integer referenes address(id),
workid integer references work(id)
}
nameid, addressid or workid in person table may be empty.
I would like to make a view which shows all information about a person
in one row
first_name, last_name, street, city, workhours, ...
In the peron table, if the workid, addressid, or nameid is empty, just
do not display the correspodent information.
I think I need left outer join, but I do not know how to use it. Could
anyone help?
Thanks,
qq
> table name
> {
> id integer primary key
> first_name text,
> middle_name text,
> last_name text
> }
>
> table address
> {
> id integer primary key
> number int,
> street text,
> city text,
> state text
> }
>
> table work
> {
> id integer primary key
> hours text,
> shift
> }
>
> table person
> {
> id integer primary key
> namid integer references name(id),
> addressid integer referenes address(id),
> workid integer references work(id)
> }
> nameid, addressid or workid in person table may be empty.
> I would like to make a view which shows all information about a person
> in one row
> first_name, last_name, street, city, workhours, ...
> In the peron table, if the workid, addressid, or nameid is empty, just
> do not display the correspodent information.
> I think I need left outer join, but I do not know how to use it. Could
> anyone help?
CREATE VIEW AS
SELECT N.first_name,
N.last_name,
A.street,
A.city,
W.hour,
....
FROM person P
LEFT JOIN ON (P.namid = N.id)
LEFT JOIN ON (P.addressid = A.id)
LEFT JOIN ON (P.workid = W.id)
;
Hopefully this is all correct and is what you are looking for?
Regards,
Richard Broersma Jr.
after an attempt at stress-testing my app i started seeing some corrupted-looking entries in the postgresql query log. for example: ... 2006-10-10 21:33:24 PDT [31175]: [509-1] LOG: statem2006-10-10 21:33:24 PDT [31166]: [4206-1] LOG: duration: 0.197 ms ent: EXECUTE <unnamed> [PREPARE: SELECT P.IS_DISPLAY, P.IS_ACTIVE, P.CITY_POSTAL_CODE_ID,P.property_name,P.address_1,P.address_2, ... 2006-10-10 21:35:11 PDT [31163]: [20258-1] LOG: statement2006-10-10 21:35:11 PDT [31179]: [218-1] LOG: duration: 57.264 ms 2006-10-10 21:35:11 PDT [31182]: [1446-1] LOG: statement: <BIND> ... this wories me in general, but in particular messes up pgfouine attempts at log analysis. any thoughts on where this might be coming from, how to deal with it, etc.? the relevant config settings are (show all | grep ' log_' output): log_connections | on log_destination | stderr log_directory | pg_log log_disconnections | on log_duration | on log_error_verbosity | default log_executor_stats | off log_filename | postgresql-%a-%H.log log_hostname | off log_line_prefix | %t [%p]: [%l-1] log_min_duration_statement | -1 log_min_error_statement | panic log_min_messages | notice log_parser_stats | off log_planner_stats | off log_rotation_age | 60 log_rotation_size | 0 log_statement | all log_statement_stats | off log_truncate_on_rotation | on
"George Pavlov" <gpavlov@mynewplace.com> writes:
> after an attempt at stress-testing my app i started seeing some
> corrupted-looking entries in the postgresql query log. for example:
It looks like you're getting messages interspersed in the log, ie,
single messages aren't written indivisibly. This doesn't affect
Postgres itself, but of course makes the log harder to read :-(
What PG version is this, on what operating system? Do you have
redirect_stderr enabled?
regards, tom lane
Taking a second look. I see a few places I made a mistake. :-(
> CREATE VIEW your_view AS
^^^^^^^^^
> SELECT N.first_name,
> N.last_name,
> A.street,
> A.city,
> W.hour,
> ....
> FROM person P
> LEFT JOIN name N ON (P.namid = N.id)
^^^^
> LEFT JOIN address A ON (P.addressid = A.id)
^^^^^^^
> LEFT JOIN work W ON (P.workid = W.id)
^^^^
> ;
Hopefully this works a little better.
Regards,
Richard Broersma Jr.
Thank you, But there seems to be syntax errors. Could you help?
Richard Broersma Jr wrote:
> > table name
> > {
> > id integer primary key
> > first_name text,
> > middle_name text,
> > last_name text
> > }
> >
> > table address
> > {
> > id integer primary key
> > number int,
> > street text,
> > city text,
> > state text
> > }
> >
> > table work
> > {
> > id integer primary key
> > hours text,
> > shift
> > }
> >
> > table person
> > {
> > id integer primary key
> > namid integer references name(id),
> > addressid integer referenes address(id),
> > workid integer references work(id)
> > }
> > nameid, addressid or workid in person table may be empty.
> > I would like to make a view which shows all information about a person
> > in one row
> > first_name, last_name, street, city, workhours, ...
> > In the peron table, if the workid, addressid, or nameid is empty, just
> > do not display the correspodent information.
> > I think I need left outer join, but I do not know how to use it. Could
> > anyone help?
>
> CREATE VIEW AS
> SELECT N.first_name,
> N.last_name,
> A.street,
> A.city,
> W.hour,
> ....
> FROM person P
> LEFT JOIN ON (P.namid = N.id)
> LEFT JOIN ON (P.addressid = A.id)
> LEFT JOIN ON (P.workid = W.id)
> ;
>
> Hopefully this is all correct and is what you are looking for?
>
> Regards,
>
> Richard Broersma Jr.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
> What PG version is this, on what operating system? Do you have > redirect_stderr enabled? Sorry, I should have included that: PostgreSQL 8.1.3 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2) And yes, redirect_stderr = on. I have no definitive way of reproducing it, just a vague one: "hit the server with lots of queries". Some solution would be very welcome because once I have a "bad" log file like that it is very hard (at least for me) to clean it up by hand. George
"George Pavlov" <gpavlov@mynewplace.com> writes:
> And yes, redirect_stderr = on. I have no definitive way of reproducing
> it, just a vague one: "hit the server with lots of queries".
Hmm. If the messages are less than PIPE_BUF bytes long (4096 bytes on
Linux) then the writes are supposed to be atomic. Can you check whether
the interspersal cases involve messages whose total length (all lines)
exceeds 4K?
regards, tom lane
> Hmm. If the messages are less than PIPE_BUF bytes long (4096 bytes on > Linux) then the writes are supposed to be atomic. Can you > check whether > the interspersal cases involve messages whose total length (all lines) > exceeds 4K? Tom, Some of them involve long messages (>4K), but there are many that do not (like the ones I had posted at the start of this thread). I am not entirely sure where to start counting? I have been counting from the last "LOG: statement: " string immediately preceding the corruption and I have sometimes >4K, sometimes under. Also what is the beginning of the message? (after the string "statement: "?) In any case I have plenty that are way under 4K. The good news is that most of these don't trip the query analysis tool I am using (pgFouine). I get about 50-80 entries like this in the logs per day and only maybe 1 in 300-400 trips the tool. Even the ones that don't trip the tool probably cause problems and skew the stats a bit, but that's, I hope, negligible. Still it would be good to get it to do the right thing. Thanks! George
"George Pavlov" <gpavlov@mynewplace.com> writes:
>> Hmm. If the messages are less than PIPE_BUF bytes long (4096 bytes on
>> Linux) then the writes are supposed to be atomic.
> Some of them involve long messages (>4K), but there are many that do not
> (like the ones I had posted at the start of this thread).
I checked around with some kernel/glibc gurus in Red Hat, and the
consensus seemed to be that we'd be better off to bypass fprintf() and
just send message strings to stderr using write() --- ie, instead of
elog.c doing
fprintf(stderr, "%s", buf.data);
do
write(fileno(stderr), buf.data, strlen(buf.data));
Anyone have any comments on possible portability risks? In particular,
will this work on Windows?
regards, tom lane
> >> Hmm. If the messages are less than PIPE_BUF bytes long
> (4096 bytes
> >> on
> >> Linux) then the writes are supposed to be atomic.
>
> > Some of them involve long messages (>4K), but there are
> many that do
> > not (like the ones I had posted at the start of this thread).
>
> I checked around with some kernel/glibc gurus in Red Hat, and
> the consensus seemed to be that we'd be better off to bypass
> fprintf() and just send message strings to stderr using
> write() --- ie, instead of elog.c doing
>
> fprintf(stderr, "%s", buf.data);
>
> do
>
> write(fileno(stderr), buf.data, strlen(buf.data));
>
> Anyone have any comments on possible portability risks? In
> particular, will this work on Windows?
Should work fine on Windows. fileno() is deprecated however, with the
following comment:
C:\Program Files\Microsoft Visual Studio
8\VC\INCLUDE\stdio.h(688) : see
declaration of 'fileno'
Message: 'The POSIX name for this item is deprecated. Instead,
use the ISO C++ conformant name: _fileno. See online help for details.'
It still works, and there is a define to get around that warning though,
so it's definitly not critical.
//Magnus
"Magnus Hagander" <mha@sollentuna.net> writes:
> Should work fine on Windows. fileno() is deprecated however, with the
> following comment:
> C:\Program Files\Microsoft Visual Studio
> 8\VC\INCLUDE\stdio.h(688) : see
> declaration of 'fileno'
> Message: 'The POSIX name for this item is deprecated. Instead,
> use the ISO C++ conformant name: _fileno. See online help for details.'
Only Microsoft would think that they get to unilaterally deprecate a
POSIX standard API :-(
We appear to be using fileno() in some dozens of places already,
so I see no reason to worry about this.
regards, tom lane
Tom Lane wrote: > "Magnus Hagander" <mha@sollentuna.net> writes: > > Should work fine on Windows. fileno() is deprecated however, with the > > following comment: > > C:\Program Files\Microsoft Visual Studio > > 8\VC\INCLUDE\stdio.h(688) : see > > declaration of 'fileno' > > Message: 'The POSIX name for this item is deprecated. Instead, > > use the ISO C++ conformant name: _fileno. See online help for details.' > > Only Microsoft would think that they get to unilaterally deprecate a > POSIX standard API :-( Yeah, these are the same guys who chose to implement select() to only work on sockets and not on plain file descriptors (for example you can't make it work on a pipe). This must be the single most far-reaching change they did to POSIX compatibility. It's incredibly idiotic and problematic. And note that it's not that it's deprecated -- it just doesn't work at all :-( -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> > > Should work fine on Windows. fileno() is deprecated however, with > > > the following comment: > > > C:\Program Files\Microsoft Visual Studio > > > 8\VC\INCLUDE\stdio.h(688) : see > > > declaration of 'fileno' > > > Message: 'The POSIX name for this item is deprecated. > > > Instead, use the ISO C++ conformant name: _fileno. See > online help for details.' > > > > Only Microsoft would think that they get to unilaterally > deprecate a > > POSIX standard API :-( Well. a) It's not a POSIX platform. b) There is a #define you can set to disable the deprecation, from what I can tell. > Yeah, these are the same guys who chose to implement select() > to only work on sockets and not on plain file descriptors > (for example you can't make it work on a pipe). This must be > the single most far-reaching change they did to POSIX > compatibility. It's incredibly idiotic and problematic. And > note that it's not that it's deprecated -- it just doesn't > work at all :-( You will notice that in the win32 API there is no such thing as a "plain file descriptor". The Windows API has you using HANDLEs for both sockets and files, and as long as you use the Windows API, you can pass them both to the same function (just use waitformultipleobjects instead of select). select() is only in the winsock library, which is there only for compatibility with Berkeley Sockets. And it works with sockets created that way... That said, it can certainly be irritating :-) Oh, and if you want it for pipes, look at some of the port code for win32 - claudio did a pipe() implementation that works fine with select(). //Magnus