[SQL] Trigger for Large Object Modification
Hello, I created a table with a OID Column to carry the identifier for a Large Object. Now i want to run a trigger is the data of the large object has changed. To do this I created an ON UPDATE-trigger on the table, but the trigger does get fired if i only change the data of the large object (not its id). Is there any way to get notified if a large object has changed? Thanks in advance, A. Roth ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] select and as doubt
Thanks for reply Tom, but which docs should I read for that ? Tom Lane escreveu: > =?ISO-8859-1?Q?Alexandre_Gon=E7alves_Jacarand=E1?= <[EMAIL PROTECTED]> writes: > >>It's possible to make an SELECT inside an AS clause ? > > > No. You could try building the query as a string. > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > -- __ Alguns caminham pelo arco, eu caminho pela reta. Alexandre Gonçalves Jacarandá Assessor de Tecnologia de Informação Tel.: 0 ** 21 8131-2313 ___ Yahoo! doce lar. Faça do Yahoo! sua homepage. http://br.yahoo.com/homepageset.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] What does "merge-joinable join conditions" mean ????
I have a FULL JOIN between two simple tables, and am getting the message " Query failed: ERROR: FULL JOIN is only supported with merge-joinable join conditions" I'd be glad to fix this in my query if I knew what the [EMAIL PROTECTED] it meant. Using 8.0.4 on FC1; willing to upgrade (PostgreSQL, not FC1) IF it would fix the problem. -- Dean ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] What does "merge-joinable join conditions" mean ????
On Sun, Jan 15, 2006 at 12:25:10PM -0800, Dean Gibson (DB Administrator) wrote: > I have a FULL JOIN between two simple tables, and am getting the message > " Query failed: ERROR: FULL JOIN is only supported with merge-joinable > join conditions" > > I'd be glad to fix this in my query if I knew what the [EMAIL PROTECTED] it > meant. What's the query? > Using 8.0.4 on FC1; willing to upgrade (PostgreSQL, not FC1) IF it > would fix the problem. The 8.0.5 Release Notes have the following item, but without seeing your query it's hard to know whether this fix is relevant to your problem (the fix mentions RIGHT JOIN; your error says FULL JOIN): * Fix longstanding planning error for outer joins This bug sometimes caused a bogus error "RIGHT JOIN is only supported with merge-joinable join conditions". Consider upgrading in any case. 8.0.6 is the latest in the 8.0 branch; see the Release Notes for a summary of fixes since 8.0.4: http://www.postgresql.org/docs/8.0/interactive/release.html#RELEASE-8-0-6 http://www.postgresql.org/docs/8.0/interactive/release-8-0-5.html -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] What does "merge-joinable join conditions" mean ????
"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes: > I have a FULL JOIN between two simple tables, and am getting the message > " Query failed: ERROR: FULL JOIN is only supported with merge-joinable > join conditions" > I'd be glad to fix this in my query if I knew what the [EMAIL PROTECTED] it > meant. The join condition(s) of a FULL JOIN have to be mergejoinable, which basically means an equality relation between sortable (btree-indexable) data types. You can see exactly which operators are mergejoinable with a query like select oid::regoperator from pg_operator where oprlsortop != 0; regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] What does "merge-joinable join conditions" mean ????
Michael Fuhr <[EMAIL PROTECTED]> writes: > The 8.0.5 Release Notes have the following item, but without seeing > your query it's hard to know whether this fix is relevant to your > problem (the fix mentions RIGHT JOIN; your error says FULL JOIN): Good catch, but that bug was specific to left/right joins (basically, the code failed to force the join to be flipped around when needed). With a FULL JOIN you're stuck ... flipping it doesn't help. The reason it's an issue is that for a FULL JOIN, the executor has to keep track of whether rows on *both* sides of the join have been matched to any rows of the other side. If there are join conditions that are outside the mergejoin list then this requires an indefinitely large amount of state. It'd be possible to teach hash join to implement FULL JOIN (basically, you'd have to add an I've-been-joined flag to each entry in the hash table, and then re-scan the hash table at the end of the join to see which inner-side rows remain unjoined). With this you'd only need one hashable join condition to make it work, whereas the FULL JOIN mergejoin code requires *all* the join conditions to be mergejoinable. The issue hasn't come up often enough to make it seem like a high-priority problem, however. I can only recall one or two people complaining about it in all the time we've had outer-join support. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] What does "merge-joinable join conditions" mean ????
On 2006-01-15 13:54, Michael Fuhr wrote: What's the query? SELECT count(*) FROM "GenAppExtra" FULL JOIN "GeoRestrict" ON callsign ~ pattern WHERE region_id = 4 OR geo_region = 4; If either of the "region_id = 4" (a field in the right-hand table) or "geo_region = 4" (a field in the left-hand table) are removed, the SELECT functions (does not give an error message), as does the case where the "callsign ~ pattern" (one field is from each table) is replaced by "callsign = pattern". What's frustrating is that both tables are small, and "GeoRestrict" is only about 15 rows. At this point, I'm considering a UNION; alternate suggestions welcome! -- Dean ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] What does "merge-joinable join conditions" mean ????
"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes: > SELECT count(*) FROM "GenAppExtra" FULL JOIN "GeoRestrict" ON callsign > ~ pattern WHERE region_id = 4 OR geo_region = 4; > If either of the "region_id = 4" (a field in the right-hand table) or > "geo_region = 4" (a field in the left-hand table) are removed, the > SELECT functions (does not give an error message), Really? The FULL JOIN condition using ~ is the source of the failure, and I'd be quite surprised if changing WHERE makes it work. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] What does "merge-joinable join conditions" mean ????
On 2006-01-15 15:21, Tom Lane wrote:
Really? The FULL JOIN condition using ~ is the source of the failure, and I'd
be quite surprised if changing WHERE makes it work.
Works fine:
EXPLAIN SELECT count(*) FROM "Extra" FULL JOIN "GeoRestrict" ON
callsign ~ pattern WHERE geo_region =
4;
QUERY
PLAN
-
Aggregate (cost=1934.02..1934.02 rows=1 width=0)
-> Nested Loop Left Join (cost=1.18..1926.66 rows=2943 width=0)
Join Filter: ("outer".callsign ~ ("inner".pattern)::text)
-> Seq Scan on "Extra" (cost=0.00..866.00 rows=2943 width=10)
Filter: (geo_region = 4)
-> Materialize (cost=1.18..1.34 rows=16 width=7)
-> Seq Scan on "GeoRestrict" (cost=0.00..1.16 rows=16
width=7)
Note that this used to be just a LEFT JOIN (which also worked), but
today I wanted to include rows from "GeoRestrict" that had nulls for the
left-hand-side of the query, so I changed the LEFT JOIN to a FULL JOIN
(that worked), and then attempted to add a condition to restrict which
rows were included from "GeoRestrict", and that gave the error.
-- Dean
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] What does "merge-joinable join conditions" mean ????
On 2006-01-15 15:42, Tom Lane wrote: EXPLAIN SELECT count(*) FROM "Extra" FULL JOIN "GeoRestrict" ON callsign ~ pattern WHERE geo_region = 4; Oh, but that reduces it to a left join, as you can see in the EXPLAIN output --- Yes, I previously noticed that in the EXPLAIN output too. any null-extension rows from the right side are going to fail the WHERE condition anyway, so the planner simplifies the FULL JOIN to a LEFT JOIN. If you'd eliminate the WHERE altogether then the failure will come back. Tried that, and you are right there as well. So, given the fact that the right-hand-table is only about 15 rows, do you think changing the SELECT back to a LEFT JOIN, and then using a UNION to get the extra right-hand-rows in, is the best work-around? -- Dean ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] What does "merge-joinable join conditions" mean ????
"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes: > So, given the fact that the right-hand-table is only about 15 rows, do > you think changing the SELECT back to a LEFT JOIN, and then using a > UNION to get the extra right-hand-rows in, is the best work-around? Yeah, a UNION of left and right joins seems the only very reasonable solution. As long as you're sure there are no duplicate rows you need to keep, it'll work well enough. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Executing plpgsql scripts using psql, is that possible?
Hi,
I would like to write some administration plpgsql scripts that populate some
tables (dimension tables) and to execute them using psql. I’m not sure that is
possible with psql as it is with Oracle sqlplus or SQL Server MSQuery:
Oracle sqlplus:
---
DECLARE
V_MyObjectID bigint;
BEGIN
V_MyObjectID := RegisterMyObject('a string', 'another string');
AddObjectProperty(V_MyObjectID, 'a string');
AddObjectProperty(V_MyObjectID, 'another string');
END;
SQL Server MSQuery:
---
DECLARE @MyObjectID int
SET @MyObjectID = RegisterMyObject('a string', 'another string')
EXECUTE AddObjectProperty(MyObjectID, 'a string');
EXECUTE AddObjectProperty(MyObjectID, 'another string');
Any idea how I can translate such a script for psql? I mean, without creating
a function that wraps the whole, of course! :-)
Thanks,
Daniel
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
