Re: [GENERAL] referring to a different database from a trigger
Scott Marlowe wrote: On Fri, Nov 21, 2008 at 3:31 PM, ries van Twisk <[EMAIL PROTECTED]> wrote: Note that there are no built in transactional symantics in such situations. You got to roll your own. And they may not work. Yeah, that was what I was hoping for. ie:(query between databases) SELECT db_one.table_one.column_name_one, db_two.table_oranges.column_fluff FROM db_one.table_one, db_two.table_oranges WHERE db_one.table_one.some_id=db_two.table_oranges.raisin_id; Oh well!, I'll just do it in code I guess I can see where the difficulties lay in this. It would require a pipeline between the two databases allowing one of them to share tables, difficult if there are common oids pointing to completely different objects. Oids/schemas would need to be aliased in some way to give them pseudo-representation in the destination database. P -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] referring to a different database from a trigger
Hello, Is there a syntax for querying another database from a trigger in the current database? Thanks for any info, P -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Compiling C Trigger function
Hello, I am attempting to compile a C trigger function for use with Postgresql. The function uses SPI and I am comiling in linux using gcc. The compiler is finding all the correct headers but complains that it can't find the SPI functions. I am compiling with -lpq . Do I need to compile with additional library flags? If so, what libraries do I need to link with? Thanks Peter -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Querying Headers
pw wrote:
Hello,
I am curious if there is a postgresql function that will
return a list of header names based on an input query text.
ie:
select return_headers("SELECT name, date, shape FROM some_table;") as
headers;
returning:
headers
---
name
date
shape
Thanks for any help.
Pw
As a supplementary comment to this:
This information can be extracted from the pg_catalog
in several steps as follows:
CREATE VIEW testview AS (SELECT name, date, shape FROM some_table);
SELECT distinct attname FROM pg_attribute, pg_class, testview WHERE
attrelid=pg_class.oid AND pg_class.relname='testview';
/*GET THE COLUMN HEADERS HERE AND RETURN THEM*/
DROP VIEW testview;
I was however hoping someone had already created a
function that was standard.
ie:
CREATE FUNCTION return_header_names(text) RETURNS SETOF string
AS '
CREATE VIEW testview AS ($1);
SELECT distinct attname FROM pg_attribute, pg_class, testview WHERE
attrelid=pg_class.oid AND pg_class.relname='testview';
'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
Thanks again,
Pw
---(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
[GENERAL] Querying Headers
Hello,
I am curious if there is a postgresql function that will
return a list of header names based on an input query text.
ie:
select return_headers("SELECT name, date, shape FROM some_table;") as
headers;
returning:
headers
---
name
date
shape
Thanks for any help.
Pw
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How do I recover from>> pg_xlog/0000000000000000 (log
Mike Nolan wrote: On Tue, Oct 19, 2004 at 03:49:04PM -0700, pw wrote: I set up a cron job to pg_dump and gzip every hour and dump any backup gz files older than 1 week. Huh ... be sure to keep some older backup anyway! There was just someone on a list (this one?) whose last two weeks of backups contained no data (a guy with OpenACS or something). Also, if you don't routinely test your backups every now and then, how can you be sure they'll work when you NEED them to? -- Mike Nolan Hello, If vacuumdb and pg_dump don't work then I have bigger problems than just a hardware burp. It's just like any other (MS incuded) software. You have to trust it until it proves otherwise. I've seen oracle go south because of hardware, etc. too. At least I'm not spending $30,000 for the adventure. I don't get any more satisfaction for the $30 grand than rebuilding from a backup anyway. If I really felt paranoid about it I could have a test server set up and make a cron job that scps the current backup over and builds a database from it. Then queries every table for the last updated record and compares it to the local server. A days work tops. I'm pretty sure the current backup method is OK though. It can even move the database backup off site in case the place burns down. In the case of the fellow with no data, It's difficult to say whether that's real or not. I moved a DB over to another machine and had to open the tar file that came from pg_dump, edit the 'restore.sql' in several places, and run the script manually so I could watch the error logging. All the data was there, it just wasn't going through the COPY command properly (path issues). Also, the proceedural language that I was using for a trigger needed to be installed by 'postgres' user *first* before I was able to make part of the script work. It's pretty easy to forget all the schema stuff in a database over time. Did that guy look in the '.dat' files to see if there was data? Peter ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] How do I recover from>> pg_xlog/0000000000000000 (log
Tom Lane wrote: peter Willis <[EMAIL PROTECTED]> writes: [EMAIL PROTECTED] /]$ LOG: database system shutdown was interrupted at 2004-10-18 11:41:55 PDT LOG: open of /web2-disk1/grip/database/pg_xlog/ (log file 0, segment 0) failed: No such file or directory LOG: invalid primary checkpoint record LOG: open of /web2-disk1/grip/database/pg_xlog/ (log file 0, segment 0) failed: No such file or directory LOG: invalid secondary checkpoint record PANIC: unable to locate a valid checkpoint record LOG: startup process (pid 2803) was terminated by signal 6 LOG: aborting startup due to startup process failure pg_resetxlog would probably get you to a point where you could start the server, but you should not have any great illusions about the consistency of your database afterward. How did you get into this state, anyway? And what PG version is it? regards, tom lane The server was running with postgres on terabyte firewire 800 drive. A tech decided to 'hot-plug' another terabyte drive into the system without downing the server, umounting the first drive, and then remounting both drives. Since ohci drivers tend to enumerate and mount without using the hardware ID of the drive , the poor kernel got confused and decided that the new drive was first in lineclang! I had a database backup from the previous day. I just used that. I set up a cron job to pg_dump and gzip every hour and dump any backup gz files older than 1 week. I love that 'date' command .. :) date +%F-%H%M%S nice :) Peter ---(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] How Do I Change The 'Owner' of a Database?
Thanks for your help, I was looking at ALTER DATABASE but the docs don't disclose any attributes so *what the heck does one ALTER?* I'll try the query that you offered. Peter Steven Klassen wrote: There might be something you can do with 'alter database' as well. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] How Do I Change The 'Owner' of a Database?
Hello, I used a user to create a database but postgreSQL insists that the 'postgres' user is the owner. It's a bit annoying to have to change users to link sequences to counter values. How can I cahnge the owner of the database to the proper user? Peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] query of database to get permitted users list
Hello, I have a query of pg_database to find the owners of each available database. How can I query to get the users that have permissions on any particular database? pg_user always contains *all* database users regardless of whether they have granted permissions to the current db. Thanks for any ideas. Peter ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL](THE_ANSWER) What is the syntax for UPDATE from one
Thanks to everyone who helped. I found the solution just by tooling with the SQL syntax. FYI, the correct syntax is: UPDATE destination_table SET dest_column=A FROM (SELECT src_column as A, src_link_col FROM src_table) AS J WHERE src_link_col=dst_link_col; Pete pw wrote: Hello, What is the proper syntax for updating a column inone table from a column in another? I have tried this: UPDATE destination_table FROM source_table SET destination_table.column_one = source_table.column_b WHERE constraint; This fails, so I must have the syntax incorrect. Thanks for any help, Pete ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(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] What is the syntax for UPDATE from one table to another?
Hello, What is the proper syntax for updating a column inone table from a column in another? I have tried this: UPDATE destination_table FROM source_table SET destination_table.column_one = source_table.column_b WHERE constraint; This fails, so I must have the syntax incorrect. Thanks for any help, Pete ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] problem running postmaster
Hello, I am having problems running postmaster. I get the following error: DEBUG: invoking IpcMemoryCreate(size=1466368) PANIC: invalid checksum in control file The postmaster then exits. What control file is it talking about? Is this a common error? Thanks for any help Peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] converting varchar date strings to date
Hello, This has been resolved. As I told a previous poster, CAST() wasn't working. I have no idea why. I finally used: UPDATE inventory SET date_field=date(vc_year||'-'||vc_month||'-'||vc_day ); Peter > pw writes: > > > How can I typecast a date generated from VARCHAR fields into > > a date field > > Using CAST(). > > -- > Peter Eisentraut [EMAIL PROTECTED] > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] converting varchar date strings to date
Hello, How can I typecast a date generated from VARCHAR fields into a date field ie: UPDATE inventory SET date_field = vc_year||'-'||vc_month||'-'||vc_day; where the date string is built up from varchar fields? Thanks for any help. Peter ---(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
