Re: [GENERAL] Casting and Timestamp
Mark Gibson wrote: I have unexpected results when trying to cast a string to a timestamp: test=# select TIMESTAMP WITH TIME ZONE '2006/06/25 06:00:00 GMT-5' test-# ; timestamptz 2006-06-24 20:00:00-05 Seems that what I get is about 10 hours earlier than I expect... Any ideas why this is happening? Did I assume too much in the cast? Answered my own question - apparently the 'GMT' portion isn't understood by postgresql: template1=# select TIMESTAMP WITH TIME ZONE '2006/06/25 06:00:00-05' template1-# template1-# ; timestamptz 2006-06-25 06:00:00-05 (1 row) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Casting and Timestamp
I have unexpected results when trying to cast a string to a timestamp: test=# select TIMESTAMP WITH TIME ZONE '2006/06/25 06:00:00 GMT-5' test-# ; timestamptz 2006-06-24 20:00:00-05 Seems that what I get is about 10 hours earlier than I expect... Any ideas why this is happening? Did I assume too much in the cast? Thanks, Mark ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] casting... adding integer to timestamp
Michael Glaesemann wrote: On Jun 25, 2006, at 14:23 , Mark Gibson wrote: SELECT play_length - INTERVAL 'play_seconds seconds' ... The column isn't interpolated into the string. Try SELECT play_length - play_seconds * INTERVAL '1 second' That worked great! Thanks! Mark ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] casting... adding integer to timestamp
If play_length is a timestamp, I can do this: SELECT play_length - INTERVAL '13 seconds' ... But what if play_seconds is a column? SELECT play_length - INTERVAL 'play_seconds seconds' ... This doesn't work. ERROR: invalid input syntax for type interval: "play_seconds seconds" Can anyone help? Thanks, Mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] string primary key
Scott Marlowe wrote: If you need a unique constraint on the text field anyway, and it's a natural key, you're generally better of using that field as the pk. However, if it's not a natually unique key, then it shouldn't be the pk, and int is a perhaps better choice. There are two VERY oppositional schools of thought on natural versus artificial keys out there, and neither side is likely to change their minds. My preference is generally for artificial keys (i.e. sequence generated ones) because I've had requirements change underfoot too many times to rely on natural keys all the time. Thanks for your answer. It sounds like your saying that in terms of performance, there is no difference between a character field pk and an integer pk. I've got a followup - The primary key for the table in question consists of 2 varchar fields: picture 'state' and 'city' where city is guaranteed to be unique within a state, and (state, city) form a unique key. This sounds like a good candidate for a sequence key. Is there a difference in terms of performance in this case? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] string primary key
Is there a disadvantage to having the primary key for a table be a text type vs. an integer type? Performance? Any difference between having a varchar or char as a primary key? My instinct tells me that an integer is preferred, but I'm looking for a more concrete answer. Thanks, Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Cache lookup failed for relation, when trying to DROP
Andrew Sullivan wrote: On Wed, Oct 06, 2004 at 05:25:58PM +0100, Mark Gibson wrote: I had to remove Slony's schema manually as I was having problems with it. I was in the process of removing all Slony related stuff, and all my slave tables when this problem occurred, and was going to start again from scratch. Did your problem happen on a replica, or on the origin? There's a current dirty, evil hack in Slony that does extremely naughty things in the catalogues on the replicas. This is slated to go away in the future, but at the moment it's possible to trip over it if you don't use Slony's own admin tools. Yes it was on the slave. After a bit more playing with Slony, I've discovered the cause. I'd created rules on a slave table before subscribing it to the master, Slony was disabling the rule from within pg_catalog, so when I manually removed Slony I had some rogue rules floating around. PostgreSQL didn't know it needed to drop the rules but it was being restricted from dropping the table by unknown deps in pg_depend. -- Mark Gibson Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 3: 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: [GENERAL] Cache lookup failed for relation, when trying to DROP
Tom Lane wrote: Mark Gibson <[EMAIL PROTECTED]> writes: Right then, I think I've got this sorted, DROP TABLE worked after a swift: DELETE FROM pg_depend WHERE objid = 5173132; There's something awfully flaky going on here. The system should never have let you get into this state in the first place: the entire point of pg_depend is that you can't delete the referenced object without deleting the referencing object. Had you been doing anything odd before this (like perhaps manually deleting catalog rows)? You mentioned having dropped the troublesome rule; did you do that by-the-book with DROP RULE, or did you just DELETE FROM pg_rewrite? I haven't modified anything manually in pg_catalog. The rule was dropped with DROP RULE. Although I have been testing out Slony-I, I'm not sure if that has delved into pg_catalog, I'll have to check with the Slony folks. I had to remove Slony's schema manually as I was having problems with it. I was in the process of removing all Slony related stuff, and all my slave tables when this problem occurred, and was going to start again from scratch. -- Mark Gibson Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Cache lookup failed for relation, when trying to DROP
Mark Gibson wrote: I'm guessing I'm gonna have to route through pg_catalog for this and delete all deps manually, but it this going to be safe? Would I be better off dumping and restoring the whole database? Right then, I think I've got this sorted, DROP TABLE worked after a swift: DELETE FROM pg_depend WHERE objid = 5173132; Then DROP SCHEMA also worked. Do you think I should be safe to continue working with the database now, without a full dump and restore? (It's not in production use, but it would take quite some time for dump/restore) Cheers. -- Mark Gibson Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Cache lookup failed for relation, when trying to DROP
Tom Lane wrote: Mark Gibson <[EMAIL PROTECTED]> writes: I kept getting the following error: ERROR: cache lookup failed for relation 4667548 This implies that something someplace still has a link to the table with that OID. You could do "\set VERBOSITY verbose" so that the code location the error occurs at gets reported; that might be enough information to guess what sort of thing is linking to the table. Or you could just dig through the system catalogs looking for 4667548 in an OID column that links to pg_class. "\set VERBOSITY verbose" gave me the following: ERROR: XX000: cache lookup failed for relation 4667548 LOCATION: getRelationDescription, dependency.c:1755 and... After sticking 4667548 everywhere an oid can be stuck, I've found an entry in the pg_rewrite table. It's from a rule I added myself, but I dropped it manually, strange that is still lurking around. So, I tried deleting the offending entries from pg_rewrite, and now get: ERROR: XX000: could not find tuple for rule 5173132 LOCATION: getObjectDescription, dependency.c:1669 I'm guessing I'm gonna have to route through pg_catalog for this and delete all deps manually, but it this going to be safe? Would I be better off dumping and restoring the whole database? -- Mark Gibson Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Commands to browse current connections and processes
Ying Lu wrote: Hello, In mysql, we use "show processlist" to see all current process. Could someone let me know in PostgreSQL, what commands that we can check the current connections and processes please? SELECT * FROM pg_catalog.pg_stat_activity; -- Mark Gibson Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Cache lookup failed for relation, when trying to DROP TABLE.
Hello, I've just encountered a problem that I don't know how to deal with. After having a play with SlonyI, I dropped the entire slony cluster schema, and then tried to drop a schema which held some slave tables for the slony cluster. I kept getting the following error: ERROR: cache lookup failed for relation 4667548 So, I cleared out everything I could manually from the schema, leaving four remaining tables that refuse to be dropped, giving the error above (the relation oid varies though). Anyone know what this means? Is there a way to forcefully remove these tables and the schema using the pg_catalog tables? Is there any other information I should provide that may help? Specs: Redhat Enterprise Linux 3 PostgreSQL 7.4.5 Slony-I 1.0.2 Cheers -- Mark Gibson Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Heritage
Sebastian Davancens wrote: Hi everyone. Im having a problem with heritage. The situation is the following: I have two tables, tbl_everyone and tbl_employees. tbl_employees inherits from tbl_everyone. In tbl_everyone, i store some information about everyone who is related with the place where i work: Name, ID (PK), Birth Date, Adress... Then, in tbl_employees i have aditional information, like area, position, etc. The problem appears when i have someone in tbl_everyone that becomes an employee. What do i have to do ? Consider that ID in tbl_everyone is used in auxiliar tables, so i cannot easily delete the person in tbl_everyone and insert it again in tbl_employees... Have you tried deferred constraints, eg: BEGIN; SET CONSTRAINTS ALL DEFERRED; DELETE ...; INSERT ...; END; I've haven't had chance to test this, but I think this could be what you're looking for. -- Mark Gibson Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Forwarding kerberos credentials
Mark Gibson wrote: Hi, I'm having intermittent problems connecting to my PostgreSQL database from PHP, using Kerberos credentials forwarded from mod_auth_kerb. [snip] The trouble is that sometimes the connection works, and sometimes it doesn't. It's very unpredictable. :( Oh, I forgot to mention, PostgreSQL logs the following error: Kerberos recvauth returned error 103 I've searched kerberos errors to find that code 103 isn't defined! Tested on: RedHat Enterprise Linux 3 & Gentoo Linux Apache 2.0.46 & 2.0.49 (prefork) mod_auth_kerb 5.0-rc5 & rc6 PHP 4.3.8 PostgreSQL 7.4.3 MIT KerberosV5 libs 1.3.3 & 1.3.4 ...and the KDC is on a Windows 2003 Server (I have no control over this) -- Mark Gibson Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Forwarding kerberos credentials
Hi,
I'm having intermittent problems connecting to my PostgreSQL database
from PHP, using Kerberos credentials forwarded from mod_auth_kerb.
- User authenticates via mod_auth_kerb,
(either Basic or Negotiate HTTP authenication)
- Kerberos credentials are stored in a file that lives for the lifetime
of the HTTP connection.
- PHP is passed a variable naming this file:
$_SERVER['KRB5CCNAME']
(eg. 'FILE:/tmp/krb5cc_apache_RcuW4s')
- Environment variable is set from PHP:
putenv("KRB5CCNAME={$_SERVER['KRB5CCNAME']}");
- Connection to PostgreSQL is attempted from PHP:
pg_connect("host=db.example.com dbname=krbtest user={$user}");
The trouble is that sometimes the connection works,
and sometimes it doesn't. It's very unpredictable. :(
Connecting with psql/kerberos works every time!
Does anyone have an idea of what could cause this?
Has anyone else tried this connection method and got it to work?
Tested on:
RedHat Enterprise Linux 3 & Gentoo Linux
Apache 2.0.46 & 2.0.49 (prefork)
mod_auth_kerb 5.0-rc5 & rc6
PHP 4.3.8
PostgreSQL 7.4.3
MIT KerberosV5 libs 1.3.3 & 1.3.4
Here is the test script I used:
$cmd = "/usr/bin/klist";
echo "\n{$cmd}:\n";
passthru($cmd);
$connstr = "host=db.example.com dbname=krb user={$user}";
echo "\nConnection String:\n{$connstr}\n\n";
$db = pg_connect($connstr);
$res = pg_query($db,"SELECT current_user, session_user;");
print_r(pg_fetch_all($res));
pg_close($db);
?>
Cheers
--
Mark Gibson
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL] Can I reset the lower bound of an array.
Hi, I've been playing about with array's, and found the concat operator '||' quite useful, apart from the fact that prepending an element places it in a lower subscript. Is there a way of either: a) prepending an element, but shifting existing elements up a subscript, so that the lower bound remains the same? eg: 1 ||> ARRAY[2,3,4] -- new operator ||> shift array and prepend or b) resetting the lower bound of the array (without affecting elements)? eg: set_array_lower(1 || ARRAY[2,3,4], 1) -- 2nd arg is the new lower bound Also, while on the topic of array's: Is there any way to specify array slices from a subscript to the beginning or end of an array? eg: element to end: array[5:*] beginning to element: array[*:5] At present it is possible by using an extreme +ve or -ve subscript value, but this isn't nice. Cheers -- Mark Gibson Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] postgresql system column errors
Ashish Mahabal wrote: Hello Hiroshi, When I try to use the column name xmin in one of my tables I get ERROR: column name "xmin" conflicts with a system column name You seem to have been affected by these errors in the past too. Had you found a solution? Anybody else have any solutions? The only thing you can do is change your column name. The manual tells you about the system columns here: http://www.postgresql.org/docs/7.4/static/ddl-system-columns.html -- Mark Gibson Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] newbie question... how do I get table structure?
Aaron Bratcher wrote:
Is there no way I can do it with a standard select command in a
different client? I don't need the indexes, just the column names/types.
For PostgreSQL 7.3 and above:
SELECT
a.attname,
format_type(a.atttypid, a.atttypmod)
FROM
pg_catalog.pg_class c INNER JOIN
pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) INNER JOIN
pg_catalog.pg_attribute a ON (a.attrelid = c.oid)
WHERE
n.nspname = '{schema_name}' AND
c.relname = '{table_name}' AND
a.attisdropped = false AND
a.attnum > 0
Replace {schema_name} and {table_name}.
--
Mark Gibson
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] ERROR: column 'xxx' does not exist (under v. 7.4.1)
Mark Gibson wrote: You need to quote the table name if it contains upper case or strange characters: SELECT "companyID" FROM app; Obviously I meant column name, but it applies to any object identifier ;) -- Mark Gibson <[EMAIL PROTECTED]> Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 3: 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: [GENERAL] ERROR: column 'xxx' does not exist (under v. 7.4.1)
Iker Arizmendi wrote: Using psql and running as the owner of the table "app" I try to access the columns of the table like so: SELECT * FROM app; which returns all the columns in the table including the one I'm interested in, which is "companyID". If instead I use something like: SELECT companyID FROM app; I get the following: ERROR: column "companyid" does not exist even though the column DOES exist (the previous query returned "companyID" as one of the column headers). Any suggestions as to what I might be missing? I'm running Postgres 7.4.1. Regards, Iker You need to quote the table name if it contains upper case or strange characters: SELECT "companyID" FROM app; -- Mark Gibson <[EMAIL PROTECTED]> Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 3: 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
[GENERAL] dblink - custom datatypes don't work
Hello,
I've been experimenting with dblink recently, and have encountered some
limitations I'd like to discuss.
I've been trying to create views of remote tables, like so:
CREATE VIEW stuff AS
SELECT *
FROM dblink(' ... remote connection info ... ',
'SELECT id, title, title_idx FROM stuff')
AS t(
id integer,
title text,
title_idx txtidx
);
But, dblink seems to have a problem with the custom datatype 'txtidx'
(from contrib/tsearch).
I get an error like this (from PostgreSQL 7.4.1):
ERROR: cache lookup failed for type 123456
Where 123456 is the pg_type.oid of the 'txtidx' type in the remote database,
which differs from the oid of the same datatype within the local database.
Are there anyways around this (other than trying to initialise the
datatypes remotely and
locally with the same oid - which would be highly impractical).
Is this a limitation of PostgreSQL or dblink?
Could dblink use type names instead of oid's?
If not, could dblink be adapted to use some kind of
remote oid -> local oid mapping table for datatypes?
I would be willing to have a poke around in dblink.c,
if someone could confirm my findings and point me in the right direction.
Cheers
--
Mark Gibson <[EMAIL PROTECTED]>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
---(end of broadcast)---
TIP 3: 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
