Re: [SQL] automatic time/user stamp - rule or trigger?
Jan Wieck wrote: A rule will not work here because rules cannot cause the same action on the same table they are called for. A-ha! I guess that's what I wasn't understanding. Triggers it is then. Thanks, -Neal ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Problem with VACUUM
Hello everybody! We have a problem with VACUUM FULL and the our PostgresSQL is very slow. On the try run the vacuum command, its returns: NOTICE: Child itemid in update-chain marked as unused - can't continue repair_frag ERROR: No one parent tuple was found vacuumdb: vacuum nube failed I think it looking for a table or an atributte of inherits but we have already droped it. The PostgresSQL version is: 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96 Thanks Marcelo. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [ADMIN] Problem with VACUUM
Marcelo Pereira Tada <[EMAIL PROTECTED]> writes: >> NOTICE: Child itemid in update-chain marked as unused - can't continue repair_frag >> ERROR: No one parent tuple was found Try updating to 7.2.4; that should fix this. regards, tom lane ---(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
[SQL] Lock timeout detection in postgres 7.3.1
Hi I have recently migrated my database from MS Sql Server to postgresql 7.3.1. In MS SQL SERVER, it is very easy to set the lock time equals to zero on ROW LEVEL. So that if some other user try to access the same data, he/she will get the error immediately. I have tried to run the same code through VB 6.0 (windows) using pgsql as database on RED HAT LINUX 8.0, the only problem i am facing is when ever a user try to access a pre LOCKED ROW, the program goes into halt until the first user executes ROLLBACK or COMMIT. Is there any way to set the LOCK TIME equals to ZERO in postgresql 7.3.1? __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] bytea
Hello, I have a table containing a field of type bytea: CREATE TABLE a_table ( a_field bytea ); How can I import a file in a SQL script? What function I can use? Thank you very much. Adrian ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] bytea
> > I have a table containing a field of type bytea: > > CREATE TABLE a_table ( > a_field bytea > ); > > How can I import a file in a SQL script? What function I can use? > The documentation says as in PostgreSQL 7.2.1 (I doubt this changed significantly since) Octets of certain values must be escaped (but all octet values may be escaped) when used as part of a string literal in an SQL statement. In general, to escape an octet, it is converted into the three-digit octal number equivalent of its decimal octet value, and preceded by two backslashes. In general it goes like this INSERT INTO a_table ( a_field ) VALUES ( '\\000\\001\\002\\003' ) ; to load the first four ASCII characters. You did not mention how your file looks like. There is also a C function available called PQescapeBytea which does all the required escaping to store memory areas in bytea columns. Refer to Command Execution Functions within libpq - C Library for details. Regards, Christoph ---(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: [SQL] bytea
Hi Christoph, Thanks for your reply. But what I want to do is loading a file of a particular path with a sql statement in psql. Why I need to care about how the file looks like? Thanks. Adrian - Original Message - From: "Christoph Haller" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, February 05, 2003 6:15 PM Subject: Re: [SQL] bytea > > > > I have a table containing a field of type bytea: > > > > CREATE TABLE a_table ( > > a_field bytea > > ); > > > > How can I import a file in a SQL script? What function I can use? > > > The documentation says as in PostgreSQL 7.2.1 (I doubt this changed > significantly since) > > Octets of certain values must be escaped (but all octet values may be > escaped) when used as part of a string literal in an SQL > statement. In general, to escape an octet, it is converted into the > three-digit octal number equivalent of its decimal octet value, and > preceded by two backslashes. > > In general it goes like this > INSERT INTO a_table ( a_field ) VALUES ( '\\000\\001\\002\\003' ) ; > to load the first four ASCII characters. > You did not mention how your file looks like. > There is also a C function available called PQescapeBytea > which does all the required escaping to store memory areas in bytea > columns. > Refer to Command Execution Functions within libpq - C Library for > details. > > Regards, Christoph > > > > ---(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 > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Postgresql To Oracle9i
Hello, Atul here From india.Anyone who knows how to mirgrate the data from postgresql7.2 to Oracle9i.Please give the idea or methood to transfer the data. Thank you. Regards, Atul... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] bytea
> > Thanks for your reply. But what I want to do is loading a file of a particular path with a sql > statement in psql. Why I need to care about how the file looks like? Thanks. > Because "non-printables" might not be properly escaped. If they are, just use the SQL COPY command as described in the doc. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Inserting a tab character
>
> --- Luke Pascoe <[EMAIL PROTECTED]> wrote:
> > I have a table which defines various possible file
> > delimiters (CHAR(1) NOT
> > NULL), for the moment it'll only contain comma and
> > tab. Inserting a comma is
> > easy, but inserting a tab is proving somewhat more
> > difficult.
> >
> > How do I do it in 'psql'?
> >
>
> --> Try using '\t' for tab.
> Example :
>INSERT INTO table1(f1) values ('\t');
>
>I'm not sure if inserting a TAB character will
> cause some side-effects for commands like COPY FROM /
> TO since these commands use tab to delimit fields.
>
If you want to be on the safe side on COPY commands,
you could change to BYTEA type.
'\t' still works on INSERT, only on retrieval it would show up as
'\011'.
Regards, Christoph
---(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
[SQL] automatic time/user stamp - rule or trigger?
I have a table that I want to keep track of the user who last modified it and the timestamp of the modification. Should I use a trigger or a rule? CREATE TABLE stampedtable ( stampedtableid SERIAL PRIMARY KEY, updatestamp timestamp NOT NULL DEFAULT now(), updateuser name NOT NULL DEFAULT current_user, sometext text ); I suspect that I want a rule, but all the examples in the documentation seem to update a separate table and not override (or add) the insert/update to the timestamp and name columns. Thanks, -Neal ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] automatic time/user stamp - rule or trigger?
On Wed, 5 Feb 2003, Neal Lindsay wrote: > I have a table that I want to keep track of the user who last modified > it and the timestamp of the modification. Should I use a trigger or a rule? > > CREATE TABLE stampedtable ( > stampedtableid SERIAL PRIMARY KEY, > updatestamp timestamp NOT NULL DEFAULT now(), > updateuser name NOT NULL DEFAULT current_user, > sometext text > ); > > I suspect that I want a rule, but all the examples in the documentation > seem to update a separate table and not override (or add) the > insert/update to the timestamp and name columns. You may want to use rules if you need rewriting. What you actually need is some sort of driver to a specific table. You could create a view to that table (to hide the accounting columns), and then create rules on that view that do the job as you wish. > > Thanks, > -Neal > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] design question: status table+log table, indexes, triggers
[postgresql-7.2, pgsql, linux] Here's a schema-design problem I've hit a few times -- it seems like there should be a better way: I have a machine table (140 rows), currently very static: machine(machine_name text NOT NULL, machine_id smallint NOT NULL, area text NOT NULL, text text NOT NULL); and a machine_log table (8400 rows), appended to ~4 times/hour: machine_log(machine_name text,date timestamp, status text, usr text, comment text); This schema seemed logical at the outset, but the most common query is: select m.machine_name, m.text, ml.status, ml.date from machine m, machine_log ml where m.machine_name=ml.machine_name and ml.date=(select max(date)from machine_log where machine_name=ml.machine_name); This takes ~25 seconds which is way too long for interactive status check. The max(date) subselect kills me -- I've tried various indexes but to no avail. It looks like I need to put status and date_changed columns into the machine table, even though that info is implicit in the machine_log table. Is there some other schema that I'm just not thinking of which neatly stores some static info about each of a list of things as well as log info of status changes of those things? Or is there some index I could create on machine_log that would do the above query fast? Finally, I've never used triggers or rules -- what's the best way to maintain the status and date_changed columns in "machine" automatically when "machine_log" is appended to? Thanks, George -- I cannot think why the whole bed of the ocean is not one solid mass of oysters, so prolific they seem. Ah, I am wandering! Strange how the brain controls the brain! -- Sherlock Holmes in "The Dying Detective" ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Postgresql To Oracle9i
Atul Pedgaonkar wrote: > > Hello, > > Atul here From india.Anyone who knows how to mirgrate the data from > postgresql7.2 to Oracle9i.Please give the idea or methood to transfer the > data. Use pg_dump to create separate schema and data (as INSERT statements) dumps. Edit the schema so that Oracle is happy with it and run the two SQL scripts. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] automatic time/user stamp - rule or trigger?
Achilleus Mantzios wrote: > > On Wed, 5 Feb 2003, Neal Lindsay wrote: > > > I have a table that I want to keep track of the user who last modified > > it and the timestamp of the modification. Should I use a trigger or a rule? > > > > CREATE TABLE stampedtable ( > > stampedtableid SERIAL PRIMARY KEY, > > updatestamp timestamp NOT NULL DEFAULT now(), > > updateuser name NOT NULL DEFAULT current_user, > > sometext text > > ); > > > > I suspect that I want a rule, but all the examples in the documentation > > seem to update a separate table and not override (or add) the > > insert/update to the timestamp and name columns. > > You may want to use rules if you need rewriting. > What you actually need is some sort of driver to a specific table. > You could create a view to that table (to hide the accounting columns), > and then create rules on that view that do the job as you wish. I'm sure you want to use a BEFORE INSERT OR UPDATE trigger that modifies NEW.updatestamp in place just before the row get's written. A rule will not work here because rules cannot cause the same action on the same table they are called for. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] design question: status table+log table, indexes, triggers
george young <[EMAIL PROTECTED]> writes: > This schema seemed logical at the outset, but the most common query is: > select m.machine_name, m.text, ml.status, ml.date >from machine m, machine_log ml >where m.machine_name=ml.machine_name and ml.date=(select max(date)from > machine_log where machine_name=ml.machine_name); You can do this a lot better with SELECT DISTINCT ON --- see the "weather report" example in the SELECT reference page. Given a suitable index it should even be pretty quick. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] to_date has beaten me...
Postgresql 7.2.3
insert
intolog_entries
values (to_date('06/Feb/2003:11:29:11 +13', 'DD/Mon/:HH24:MI:SS'),0,302,
[... various other data elided ...]);
Always inserts the correct date, but sets the time to midnight.
Thinking this might be solved by shuffling with the source data, I've
tried such combinations as:
to_date('06/Feb/2003:11:29:11', 'DD/Mon/:HH24:MI:SS')
to_date('06/Feb/2003:11:29:11 +13', 'DD/Mon/:HH:MI:SS')
to_date('06/02/2003:11:29:11', 'DD/MM/:HH24:MI:SS')
...and so forth, but despite the column in question being a timestamp
with timszone, everything except the date gets truncated.
--
Rodger Donaldson[EMAIL PROTECTED]
"My ATEX terminal isn't working"
"Is there power to the keyboard?"
"No, and it has smoke and flames coming out of it"
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] to_date has beaten me...
Rodger Donaldson <[EMAIL PROTECTED]> writes:
> values(to_date('06/Feb/2003:11:29:11 +13', 'DD/Mon/:HH24:MI:SS'),0,302,
> Always inserts the correct date, but sets the time to midnight.
Um, well, yeah ... it's to_DATE, meaning it produces a result of type
date. I think you wanted to_timestamp().
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] to_date has beaten me...
On Wed, Feb 05, 2003 at 11:22:57PM -0500, Tom Lane wrote:
> Rodger Donaldson <[EMAIL PROTECTED]> writes:
> > values (to_date('06/Feb/2003:11:29:11 +13', 'DD/Mon/:HH24:MI:SS'),0,302,
>
> > Always inserts the correct date, but sets the time to midnight.
>
> Um, well, yeah ... it's to_DATE, meaning it produces a result of type
> date. I think you wanted to_timestamp().
Why yes. Yes I did. Too much time spent with Oracle, evidently.
Next I'll be wondering why DECODE doesn't work...
--
Rodger Donaldson[EMAIL PROTECTED]
"How do I set my laser printer for stun?"
-- William Tansil
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] to_date has beaten me...
On Thu, Feb 06, 2003 at 05:25:39PM +1300, Rodger Donaldson wrote:
> On Wed, Feb 05, 2003 at 11:22:57PM -0500, Tom Lane wrote:
> > Rodger Donaldson <[EMAIL PROTECTED]> writes:
> > > values(to_date('06/Feb/2003:11:29:11 +13', 'DD/Mon/:HH24:MI:SS'),0,302,
> >
> > > Always inserts the correct date, but sets the time to midnight.
> >
> > Um, well, yeah ... it's to_DATE, meaning it produces a result of type
> > date. I think you wanted to_timestamp().
>
> Why yes. Yes I did. Too much time spent with Oracle, evidently.
> Next I'll be wondering why DECODE doesn't work...
Because DECODE is non-standard AFAIK. You can use CASE WHEN in PostgreSQL,
which IIRC, started being supported in Oracle 8.1.7.
-Roberto
--
+|Roberto Mello -http://www.brasileiro.net/ |--+
+ Computer Science Graduate Student, Utah State University +
+ USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ +
The linuX Files -- The Source is Out There.
-- Sent in by Craig S. Bell, [EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
