Re: [HACKERS] PHP 4.0.4pl1 / Beta 5
FWIW, I emailed Thies about the pg_connect problems, and whis is what he
responded with (yesterday would be Feb 13):
i've commited a fix for this to PHP 4 CVS yesterday.
if you don't want to live on the "bleeding edge" (use PHP
from CVS) just replace the php_pgsql_set_default_link
function in pgsql.c against this one and you're all-set!
regards,
tc
static void php_pgsql_set_default_link(int id)
{
PGLS_FETCH();
if ((PGG(default_link) != -1) (PGG(default_link) != id)) {
zend_list_delete(PGG(default_link));
}
if (PGG(default_link) != id) {
PGG(default_link) = id;
zend_list_addref(id);
}
}
-
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Sun, 18 Feb 2001, Bruce Momjian wrote:
[ Charset ISO-8859-1 unsupported, converting... ]
I sure hope it gets more attention than some of the other PHP PostgreSQL
bugs.. I don't mean to trash anyone here but the pg_connect problem has been
around since 4.0.1 and has yet to be addressed. One of our programmers is
taking a look at that one but he's not been able to fix it yet.
I have worked with Thies on getting persistent connections to work
better. If there are any PostgreSQL problems with PHP, I recommend
sending something to him as he is focused on PostgreSQL recently.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: AW: [HACKERS] Three types of functions, ala function redux.
Acutally, a function can use an index scan *if* it is marked as cacheable: (the "test" table has 1 field, col (type is int4), which is populated with numbers 1 thru 5000) testdb=# create function func_test_cache (int4) returns int4 as ' testdb'# select $1; testdb'# ' LANGUAGE 'sql' with (iscachable); CREATE testdb=# create function func_test (int4) returns int4 as ' testdb'# select $1; testdb'# ' LANGUAGE 'sql'; CREATE testdb=# vacuum analyze; VACUUM testdb=# explain select * from test where col = func_test_cache(1); NOTICE: QUERY PLAN: Index Scan using idxtest on test (cost=0.00..2.01 rows=1 width=4) EXPLAIN testdb=# explain select * from test where col = func_test(1); NOTICE: QUERY PLAN: Seq Scan on test (cost=0.00..100.00 rows=1 width=4) EXPLAIN Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Thu, 21 Dec 2000, mlw wrote: Zeugswetter Andreas SB wrote: select * from table where col = function() ; (2) "function()" returns a number of values that are independent of the query. Postgres should be able to optimize this to be: "select * from table where col in (val1, val2, val3, ..valn)." I guess Postgres can loop until done, using the isDone flag? I think the above needs a different sql statement to begin with. The "= function()" clearly states that function is only allowed to return one row. The following syntax currently works, and is imho sufficient: select * from table where col in (select function()); Both syntaxes work, but always force a table scan. If you have an index on 'col' it will not be used. If your table has millions of records, this takes time. -- http://www.mohawksoft.com
RE: [HACKERS] SQL to retrieve FK's, Update/Delete action, etc. (fwd)
Here's the query that, given the primary key table, lists all foreign keys, their tables, the RI type, and defereability. Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio SELECT pg_trigger.tgargs, pg_trigger.tgnargs, pg_trigger.tgdeferrable, pg_trigger.tginitdeferred, pg_proc.proname, pg_proc_1.proname FROM pg_class pg_class, pg_class pg_class_1, pg_class pg_class_2, pg_proc pg_proc, pg_proc pg_proc_1, pg_trigger pg_trigger, pg_trigger pg_trigger_1, pg_trigger pg_trigger_2 WHERE pg_trigger.tgconstrrelid = pg_class.oid AND pg_trigger.tgrelid = pg_class_1.oid AND pg_trigger_1.tgfoid = pg_proc_1.oid AND pg_trigger_1.tgconstrrelid = pg_class_1.oid AND pg_trigger_2.tgconstrrelid = pg_class_2.oid AND pg_trigger_2.tgfoid = pg_proc.oid AND pg_class_2.oid = pg_trigger.tgrelid AND ((pg_class.relname='PRIMARY KEY TABLE') AND (pg_proc.proname Like '%upd') AND (pg_proc_1.proname Like '%del') AND (pg_trigger_1.tgrelid=pg_trigger.tgconstrrelid) AND (pg_trigger_2.tgrelid = pg_trigger.tgconstrrelid)) On Tue, 5 Dec 2000, Christopher Kings-Lynne wrote: Thanks mike - chances are it will be committed to phpPgAdmin by the end of the week! BTW, you may wish to make sure that your email as cc'd to the hacker's list as well. Regards, Chris -- Christopher Kings-Lynne Family Health Network (ACN 089 639 243) -Original Message- From: Michael Fork [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 05, 2000 12:25 PM To: Christopher Kings-Lynne Subject: RE: [HACKERS] SQL to retrieve FK's, Update/Delete action, etc. There ya go, I figured it out :) Given the name a table, this query will return all foreign keys in that table, the table the primary key is in, the name of the primary key, if the are deferrable, if the are initially deffered, and the action to be performed (RESTRICT, SET NULL, etc.). To get the foreign keys and primary keys and tables, you must parse the null-terminated pg.tgargs. When I get the equivalent query working for primary keys I will send it your way -- or if you beat me to it, send it my way (I am working on some missing functionality from the ODBC driver) Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio SELECT pt.tgargs, pt.tgnargs, pt.tgdeferrable, pt.tginitdeferred, pg_proc.proname, pg_proc_1.proname FROM pg_class pc, pg_proc pg_proc, pg_proc pg_proc_1, pg_trigger pg_trigger, pg_trigger pg_trigger_1, pg_proc pp, pg_trigger pt WHERE pt.tgrelid = pc.oid AND pp.oid = pt.tgfoid AND pg_trigger.tgconstrrelid = pc.oid AND pg_proc.oid = pg_trigger.tgfoid AND pg_trigger_1.tgfoid = pg_proc_1.oid AND pg_trigger_1.tgconstrrelid = pc.oid AND ((pc.relname='FOREIGN TABLE') AND (pp.proname LIKE '%%ins') AND (pg_proc.proname LIKE '%%upd') AND (pg_proc_1.proname LIKE '%%del') AND (pg_trigger.tgrelid=pt.tgconstrrelid) AND (pg_trigger_1.tgrelid = pt.tgconstrrelid)) On Tue, 5 Dec 2000, Christopher Kings-Lynne wrote: Hi Michael, I am on the phpPgAdmin development team, and I have been wanting to add this functionality to phpPgAdmin. I will start working with your query as soon as possible, and I will use phpPgAdmin as a testbed for the functionality. I really appreciate having your query as a working basis, because it's really hard trying to figure out the system tables! Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Michael Fork Sent: Sunday, December 03, 2000 12:23 PM To: [EMAIL PROTECTED] Subject: [HACKERS] SQL to retrieve FK's, Update/Delete action, etc. Given the name of a table, I need to find all foreign keys in that table and the table/column that they refer to, along with the action to be performed on update/delete. The following query works, but only when there is 1 foreign key in the table, when there is more than 2 it grows exponentially -- which means I am missing a join. However, given my limitied knowledge about the layouts of the postgres system tables, and the pg_trigger not being documented on the web site, I have been unable to get the correct query. Is this possible, and if so, what join(s) am I missing? SELECT pt.tgargs, pt.tgnargs, pt.tgdeferrable, pt.tginitdeferred, pg_proc.proname, pg_proc_1.proname FROM pg_class pc, pg_proc pg_proc, pg_proc pg_proc_1, pg_trigger pg_trigger, pg_trigger pg_trigger_1, pg_proc pp, pg_trigger pt WHERE pt.tgrelid = pc.oid AND pp.oid = pt.tgfoid AND pg_trigger.tgconstrrelid = pc.oid AND pg_proc.oid = pg_trigger.tgfoid AND pg_trigger_1.tgfoid = pg_proc_1.oid AND pg_trigger_1.tgconstrrelid = pc.oid AND ((pc.relname='tblmidterm') AND (pp.proname LIKE '%ins') AND (pg_proc.proname LIKE '%upd') AND (pg_proc_1.proname
Re: [HACKERS] beta testing version
Judging by the information below, taken *directly* from PostgreSQL, Inc. website, it appears that they will be releasing all code into the main source code branch -- with the exception of "Advanced Replication and Distributed Information capabilities" (to which capabilities they are referring is not made clear) which may remain proprietary for up to 24 months "in order to assist us in recovering development costs and continue to provide funding for our other Open Source contributions." I have interpreted this to mean that basic replication (server - server, server - client, possibly more) will be available shortly for Postgres (with the release of 7.1?) and that those more advanced features will follow behind. This is one of the last features that was missing from Postgres (along with recordset returning functions and clusters, among others) that was holding it back from the enterprise market -- and I do not blame PostgreSQL, Inc. one bit for withholding some of the more advanced features to recoup their development costs -- it was *their time* and *their money* they spent developing the *product* and it must be recoup'ed for projects like this to make sense in the future (who knows, maybe next they will implement RS returning SP's or clusters, projects that are funded with their profit off the advanced replication and distributed information capabilities that they *may* withhold -- would people still be whining then?) Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio (http://www.pgsql.com/press/PR_5.html) "At the moment we are limiting our test groups to our existing Platinum Partners and those clients whose requirements include these features." advises Jeff MacDonald, VP of Support Services. "We expect to have the source code tested and ready to contribute to the open source community before the middle of October. Until that time we are considering requests from a number of development companies and venture capital groups to join us in this process." Davidson explains, "These initial Replication functions are important to almost every commercial user of PostgreSQL. While we've fully funded all of this development ourselves, we will be immediately donating these capabilities to the open source PostgreSQL Global Development Project as part of our ongoing commitment to the PostgreSQL community." http://www.erserver.com/ eRServer development is currently concentrating on core, universal functions that will enable individuals and IT professionals to implement PostgreSQL ORDBMS solutions for mission critical datawarehousing, datamining, and eCommerce requirements. These initial developments will be published under the PostgreSQL Open Source license, and made available through our sites, Certified Platinum Partners, and others in PostgreSQL community. Advanced Replication and Distributed Information capabilities are also under development to meet specific business and competitive requirements for both PostgreSQL, Inc. and clients. Several of these enhanced PostgreSQL, Inc. developments may remain proprietary for up to 24 months, with availability limited to clients and partners, in order to assist us in recovering development costs and continue to provide funding for our other Open Source contributions. On Sun, 3 Dec 2000, Hannu Krosing wrote: The Hermit Hacker wrote: IIRC, this thread woke up on someone complaining about PostgreSQl inc promising to release some code for replication in mid-october and asking for confirmation that this is just a schedule slip and that the project is still going on and going to be released as open source. What seems to be the answer is: "NO, we will keep the replication code proprietary". I have not seen this answer myself, but i've got this impression from the contents of the whole discussion. Do you know if this is the case ? --- Hannu
RE: [HACKERS] SQL to retrieve FK's, Update/Delete action, etc. (fwd)
There ya go, I figured it out :) Given the name a table, this query will return all foreign keys in that table, the table the primary key is in, the name of the primary key, if the are deferrable, if the are initially deffered, and the action to be performed (RESTRICT, SET NULL, etc.). To get the foreign keys and primary keys and tables, you must parse the null-terminated pg.tgargs. When I get the equivalent query working for primary keys I will send it your way -- or if you beat me to it, send it my way (I am working on some missing functionality from the ODBC driver) Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio SELECT pt.tgargs, pt.tgnargs, pt.tgdeferrable, pt.tginitdeferred, pg_proc.proname, pg_proc_1.proname FROM pg_class pc, pg_proc pg_proc, pg_proc pg_proc_1, pg_trigger pg_trigger, pg_trigger pg_trigger_1, pg_proc pp, pg_trigger pt WHERE pt.tgrelid = pc.oid AND pp.oid = pt.tgfoid AND pg_trigger.tgconstrrelid = pc.oid AND pg_proc.oid = pg_trigger.tgfoid AND pg_trigger_1.tgfoid = pg_proc_1.oid AND pg_trigger_1.tgconstrrelid = pc.oid AND ((pc.relname='FOREIGN TABLE') AND (pp.proname LIKE '%%ins') AND (pg_proc.proname LIKE '%%upd') AND (pg_proc_1.proname LIKE '%%del') AND (pg_trigger.tgrelid=pt.tgconstrrelid) AND (pg_trigger_1.tgrelid = pt.tgconstrrelid)) On Tue, 5 Dec 2000, Christopher Kings-Lynne wrote: Hi Michael, I am on the phpPgAdmin development team, and I have been wanting to add this functionality to phpPgAdmin. I will start working with your query as soon as possible, and I will use phpPgAdmin as a testbed for the functionality. I really appreciate having your query as a working basis, because it's really hard trying to figure out the system tables! Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Michael Fork Sent: Sunday, December 03, 2000 12:23 PM To: [EMAIL PROTECTED] Subject: [HACKERS] SQL to retrieve FK's, Update/Delete action, etc. Given the name of a table, I need to find all foreign keys in that table and the table/column that they refer to, along with the action to be performed on update/delete. The following query works, but only when there is 1 foreign key in the table, when there is more than 2 it grows exponentially -- which means I am missing a join. However, given my limitied knowledge about the layouts of the postgres system tables, and the pg_trigger not being documented on the web site, I have been unable to get the correct query. Is this possible, and if so, what join(s) am I missing? SELECT pt.tgargs, pt.tgnargs, pt.tgdeferrable, pt.tginitdeferred, pg_proc.proname, pg_proc_1.proname FROM pg_class pc, pg_proc pg_proc, pg_proc pg_proc_1, pg_trigger pg_trigger, pg_trigger pg_trigger_1, pg_proc pp, pg_trigger pt WHERE pt.tgrelid = pc.oid AND pp.oid = pt.tgfoid AND pg_trigger.tgconstrrelid = pc.oid AND pg_proc.oid = pg_trigger.tgfoid AND pg_trigger_1.tgfoid = pg_proc_1.oid AND pg_trigger_1.tgconstrrelid = pc.oid AND ((pc.relname='tblmidterm') AND (pp.proname LIKE '%ins') AND (pg_proc.proname LIKE '%upd') AND (pg_proc_1.proname LIKE '%del')) Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio
[HACKERS] RI Types
I am trying to set the update and delete rules that are returned from the ODBC driver and the spec has the following to say: SQL_NO_ACTION: If a delete of a row in the referenced table would cause a "dangling reference" in the referencing table (that is, rows in the referencing table would have no counterparts in the referenced table), then the update is rejected. (This action is the same as the SQL_RESTRICT action in ODBC 2.x.) What I need to know is if RI_FKey_noaction_del and RI_FKey_restrict_del procedures are functionally the same. The ODBC (which I would hope conforms to SQL 9x) spec has 4 types of RI (CASCADE, NO_ACTION, SET_NULL, SET_DEFAULT), and Postgres appears to have 5 (RI_FKey_cascade_del, RI_FKey_noaction_del, RI_FKey_restrict_del, RI_FKey_setdefault_del, RI_FKey_setnull_del), which leads me to belive that restrict and noaction are the same thing, and the one that is used depends on what the user puts in the REFERENCES line. Am I correct? Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Fri, 1 Dec 2000, Stephan Szabo wrote: It's representing a single null I believe. I'm not sure if in general it's an octal or decimal number but 3 digits for the value of the character. Stephan Szabo [EMAIL PROTECTED] On Fri, 1 Dec 2000, Michael Fork wrote: What are these characters: \000 are they 3 nulls? a null followed by 2 zeros? The reason I have been asking is that I am adding foreign key support to the ODBC driver :)
[HACKERS] ODBC Driver
I am curious as to where the newest ODBC driver source is -- I retrieved /src/interfaces/odbc from CVS, but it appeared to only be version 6.40.0009 and was lacking the Visual C++ workspace/project files that were in the 6.50. release zip file on the FTP server. Thanks Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio
Re: [HACKERS]
try this
SELECT age(max(h_date), now()) FROM table WHERE email='hawks@vsnl';
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Thu, 30 Nov 2000, Manish Vig wrote:
Dear Sir,
thanks for the reply.
I tried select now()
but it gives the following error
syntax error near unexpected token `select.
To be specific about my problem, I want to compare one max date with the
current date in my Java servlet
Since nested queries are not possible, how do i acheive my goal.
My present query doesn't works and is like this
Select months_between(('select max(h_date ) from query where
email="[EMAIL PROTECTED]"),(select sysdate from dual)) from query
What is the SQL query that can acheive the same effect.
With Best Regards
SanjayArora
[HACKERS] pg_trigger and tgargs
I was wondering if someone could tell me if I have gotten the fields of tgargs correct: unnamed\000 -- Constraint name? foreign_table_multi\000 -- table with foreign key(s) primary_table_multi\000 -- table with primary key(s) UNSPECIFIED\000 -- ?? foreign_int_1\000 -- 1st field in foreign key primary_int_1\000 -- 1st field in referenced primary key foreign_int_2\000 -- 1st field in foreign key primary_int_2\000 -- 1st field in referenced primary key Thanks Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio
[HACKERS] Query returning incorrect results
When I execute the following two queries, the results differ -- with the only change being that another table is joined (a 1-1 join that should not affect the results -- I reduced down a much larger query that was exhibiting the behavior to what appears to be the cause). I know that views have some limitations, and two of the relations used are views, so I belive that that may be the problem, but I want to be sure... Thanks Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio football=# SELECT play.play_id as play_id, year.correct_picks as ytd_correct_picks FROM tblweek_correct correct, tblplayer play, tblyear_correct year WHERE play.play_id = year.play_id AND play.play_id = correct.play_id AND correct.game_week = 6 AND play.play_id = 4; play_id | ytd_correct_picks -+--- 4 | 141 (1 row) football=# SELECT play.play_id as play_id, year.correct_picks as ytd_correct_picks FROM tblplayer play, tblyear_correct year WHERE play.play_id = year.play_id AND play.play_id = 4; play_id | ytd_correct_picks -+--- 4 |47 (1 row) football=# EXPLAIN SELECT play.play_id as play_id, year.correct_picks as ytd_correct_picks FROM tblweek_correct correct, tblplayer play, tblyear_correct year WHERE play.play_id = year.play_id AND play.play_id = correct.play_id AND correct.game_week = 6 AND play.play_id = 4; NOTICE: QUERY PLAN: Aggregate (cost=108.40..108.41 rows=0 width=64) - Group (cost=108.40..108.40 rows=1 width=64) - Sort (cost=108.40..108.40 rows=1 width=64) - Nested Loop (cost=0.00..108.39 rows=1 width=64) - Nested Loop (cost=0.00..106.36 rows=1 width=56) - Nested Loop (cost=0.00..104.33 rows=1 width=52) - Nested Loop (cost=0.00..16.54 rows=1 width=40) - Nested Loop (cost=0.00..14.75 rows=1 width=36) - Nested Loop (cost=0.00..13.30 rows=1 width=32) - Nested Loop (cost=0.00..11.52 rows=1 width=28) - Nested Loop (cost=0.00..5.11 rows=1 width=16) - Seq Scan on tblgame game (cost=0.00..2.08 rows=1 width=8) - Index Scan using tblgame_winner_pkey on tblgame_winner winner (cost=0.00..2.01 rows=1 width=8) - Index Scan using tblpick_gameid_playid on tblpick pick (cost=0.00..5.05 rows=3 width=12) - Seq Scan on tblplayer play (cost=0.00..1.35 rows=35 width=4) - Seq Scan on tblplayer play (cost=0.00..1.44 rows=1 width=4) - Seq Scan on tblplayer play (cost=0.00..1.35 rows=35 width=4) - Seq Scan on tblpick pick (cost=0.00..51.24 rows=2924 width=12) - Index Scan using tblgame_pkey on tblgame game (cost=0.00..2.01 rows=1 width=4) - Index Scan using tblgame_winner_pkey on tblgame_winner winner (cost=0.00..2.01 rows=1 width=8) EXPLAIN football=# EXPLAIN SELECT play.play_id as play_id, year.correct_picks as ytd_correct_picks FROM tblplayer play, tblyear_correct year WHERE play.play_id = year.play_id AND play.play_id = 4; NOTICE: QUERY PLAN: Aggregate (cost=101.12..101.12 rows=0 width=32) - Group (cost=101.12..101.12 rows=1 width=32) - Sort (cost=101.12..101.12 rows=1 width=32) - Hash Join (cost=96.27..101.11 rows=1 width=32) - Seq Scan on tblgame_winner winner (cost=0.00..1.72 rows=72 width=8) - Hash (cost=96.26..96.26 rows=4 width=24) - Hash Join (cost=3.40..96.26 rows=4 width=24) - Nested Loop (cost=0.00..91.02 rows=19 width=20) - Nested Loop (cost=0.00..3.23 rows=1 width=8) - Seq Scan on tblplayer play (cost=0.00..1.44 rows=1 width=4) - Seq Scan on tblplayer play (cost=0.00..1.35 rows=35 width=4) - Seq Scan on tblpick pick (cost=0.00..51.24 rows=2924 width=12) - Hash (cost=1.86..1.86 rows=86 width=4) - Seq Scan on tblgame game (cost=0.00..1.86 rows=86 width=4) EXPLAIN football=# \d tblweek_correct View "tblweek_correct" Attribute | Type | Modifier ---+-+-- play_id | integer | game_week | integer | correct_picks | integer | View definition: SELECT play.play_id, game
