pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedVIEWs and TEMP tables problem
4+ messages / 4 participants
[nested] [flat]
* VIEWs and TEMP tables problem
@ 2006-02-16 11:26 Antal Attila <atesz@ritek.hu>
2006-02-16 11:57 ` Re: VIEWs and TEMP tables problem Richard Huxton <dev@archonet.com>
2006-02-16 12:13 ` Re: VIEWs and TEMP tables problem Markus Schaber <schabi@logix-tt.com>
0 siblings, 2 replies; 4+ messages in thread
From: Antal Attila @ 2006-02-16 11:26 UTC (permalink / raw)
To: pgsql-sql
Hi!
I found a problem with the views in PostgreSQL if I want to use
temporary tables in it. See the next case!
CREATE TABLE a(...., code INT4,...);
INSERT INTO a(...,code,...) VALUES (...,23,...);
CREATE TABLE actual_code(code INT4);
If I execute the next query, the result is empty.
SELECT * FROM actual_code;
CREATE VIEW a_view AS SELECT * FROM a JOIN actual_code AC ON (a.code =
AC.code);
CREATE TEMP TABLE actual_code AS SELECT 23::INT4 AS code;
If I execute the next query, the result contains exactly one row (code:
23). This is perfect.
SELECT * FROM actual_code;
After it if I compare the the next two queries, there will be
differences in the results.
1) SELECT * FROM a JOIN actual_code AC ON (a.code = AC.code); (Result
has one row!)
2) SELECT * FROM a_view; (Result is empty!)
In my opinion this queries should be equivalent with same results.
The problem is that the view use the original permanent table, but the
1) query use the temporary actual_code table.
I read the temporally table definition in the documentation
(http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html)
and I concluded it should be working.
Is it BUG, or FEATURE? Has anybody got good ideas for solve this problem?
This construction came to my mind, because I tried to solve my another
question:
http://groups.google.co.hu/group/pgsql.performance/browse_thread/thread/c7aec005f4a1f3eb/83fa0053cad...
Thanks your ideas!
Regards,
Antal Attila
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: VIEWs and TEMP tables problem
2006-02-16 11:26 VIEWs and TEMP tables problem Antal Attila <atesz@ritek.hu>
@ 2006-02-16 11:57 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2006-02-16 11:57 UTC (permalink / raw)
To: Antal Attila <atesz@ritek.hu>; +Cc: pgsql-sql
Antal Attila wrote:
> Hi!
>
> I found a problem with the views in PostgreSQL if I want to use
> temporary tables in it. See the next case!
[snip]
> The problem is that the view use the original permanent table, but the
> 1) query use the temporary actual_code table.
> I read the temporally table definition in the documentation
> (http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html)
> and I concluded it should be working.
>
> Is it BUG, or FEATURE? Has anybody got good ideas for solve this problem?
Feature, I'd guess.
When the view was built there was no TEMP TABLE. If you do \d a_view
you'll see it actually will link to something like "public.a". If it
didn't, your view could change depending on your search_path settings
and even break.
You'll see a similar problem with plpgsql functions.
> This construction came to my mind, because I tried to solve my another
> question:
> http://groups.google.co.hu/group/pgsql.performance/browse_thread/thread/c7aec005f4a1f3eb/83fa0053cad...
I think you need to explain why you're trying to do this:
CREATE VIEW ab_view AS
SELECT a.id AS id,
a.userid AS userid_a, b.userid AS userid_b,
a.col AS col_a, b.col AS col_b
FROM a LEFT JOIN b ON (a.id = b.a_id);
EXPLAIN ANALYSE SELECT * FROM ab_view
WHERE userid_a = 23 AND userid_b = 23 AND col_a LIKE 's%'
ORDER BY col_b
LIMIT 10 OFFSET 10;
If you want userid_a=userid_b ALWAYS to be the same, just put it in the
view. If you sometimes want them different, then you'll have to provide
two parameters anyway. It's not clear how you intend to use this.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: VIEWs and TEMP tables problem
2006-02-16 11:26 VIEWs and TEMP tables problem Antal Attila <atesz@ritek.hu>
@ 2006-02-16 12:13 ` Markus Schaber <schabi@logix-tt.com>
2006-02-16 15:05 ` Re: VIEWs and TEMP tables problem Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 4+ messages in thread
From: Markus Schaber @ 2006-02-16 12:13 UTC (permalink / raw)
To: Antal Attila <atesz@ritek.hu>; +Cc: pgsql-sql
Hi, Antal,
Antal Attila wrote:
> CREATE VIEW a_view AS SELECT * FROM a JOIN actual_code AC ON (a.code =
> AC.code);
Here, you bind the View to the permanent table.
> CREATE TEMP TABLE actual_code AS SELECT 23::INT4 AS code;
And here you create the temp table that will hide the permanent table.
> I read the temporally table definition in the documentation
> (http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html)
> and I concluded it should be working.
I would not think so, as it states "unless they are referenced with
schema-qualified names". So the permanent table is not removed, only
hidden in the default (non-qualified) name space.
Views don't look up the tables by name. Views bind to the table via
internal ids, and this binding is strong enough to survive even a table
rename, still referencing the same table.
> Is it BUG, or FEATURE? Has anybody got good ideas for solve this problem?
It is a feature.
> This construction came to my mind, because I tried to solve my another
> question:
> http://groups.google.co.hu/group/pgsql.performance/browse_thread/thread/c7aec005f4a1f3eb/83fa0053cad...
Maybe you try:
CREATE VIEW ab_view AS
SELECT a.id AS id,
a.userid AS userid,
a.col AS col_a, b.col AS col_b
FROM a LEFT JOIN b ON (a.id = b.a_id AND a.userid=b.userid);
EXPLAIN ANALYSE SELECT * FROM ab_view
WHERE userid = 23 AND col_a LIKE 's%'
ORDER BY col_b
LIMIT 10 OFFSET 10;
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: VIEWs and TEMP tables problem
2006-02-16 11:26 VIEWs and TEMP tables problem Antal Attila <atesz@ritek.hu>
2006-02-16 12:13 ` Re: VIEWs and TEMP tables problem Markus Schaber <schabi@logix-tt.com>
@ 2006-02-16 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2006-02-16 15:05 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: Antal Attila <atesz@ritek.hu>; pgsql-sql
Markus Schaber <schabi@logix-tt.com> writes:
> Antal Attila wrote:
>> Is it BUG, or FEATURE? Has anybody got good ideas for solve this problem?
> It is a feature.
Right. Note that one possible answer is, after you make the temp table,
to create a temp view referencing the temp table (and hiding the
permanent view in the same way as the temp table hides the permanent table).
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-02-16 15:05 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-16 11:26 VIEWs and TEMP tables problem Antal Attila <atesz@ritek.hu>
2006-02-16 11:57 ` Richard Huxton <dev@archonet.com>
2006-02-16 12:13 ` Markus Schaber <schabi@logix-tt.com>
2006-02-16 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox