Re: [GENERAL] preserving data after updates

[Tzahi Fadida]

Its called a "temporal database".
Usually its intended for medical or police databases where 
you need a hind sight. i.e. if today is 31/12/2005, what did we know at
20/12/2005.
for example, for a doctor appearing at court and required to testify
what he knew at 20/12/2005. 
Very cool.
It would be nice if postgreSQL could have a switch that
could turn it into a temporal database.

Regards,
tzahi.

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of Scott Frankel
> Sent: Friday, March 04, 2005 1:51 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] preserving data after updates
> 
> 
> 
> Is there a canonical form that db schema designers use
> to save changes to the data in their databases?
> 
> For example, given a table with rows of data, if I UPDATE
> a field in a row, the previous value is lost.  If I wanted to 
> track the changes to my data over time, it occurs to me that I could,
> 
> 1) copy the whole row of data using the new value, thus
>   leaving the old row intact in the db for fishing expeditions,
>   posterity, &c.
>   -- awfully wasteful, especially with binary data
> 
> 2) enter a new row that contains only new data fields, requiring
>   building a full set of data through heavy lifting and multiple 
> queries
>   through 'n' number of old rows
>   -- overly complex query design probably leading to errors
> 
> 3) create a new table that tracks changes
>   -- the table is either wide enough to mirror all columns in
>   the working table, or uses generic columns and API tricks to
>   parse token pair strings, ...
> 
> 4) other?
> 
> Thanks
> Scott
> 
> 
> ---(end of 
> broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
> 
> 



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] IDENT Authentication

[Ragnar Hafstað]

On Fri, 2005-03-04 at 19:59 -0500, David A. Cobb wrote:
> I've newly transitioned from Windoze to Debian Linux.  And, of course, 
> I'm installing things left and right.
> 
> I had pgsql up and running, then I had to do a lot of tearing out and 
> reinstalling other stuff.  Now, when I try to connect to psql I get:
> 
>  psql: FATAL:  IDENT authentication failed for user "superbiskit"

edit /etc/postgresql/pg_hba.conf
and do a sudo /etc/init.d/postgresql reload

gnari



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] IDENT Authentication

[David A. Cobb]

I've newly transitioned from Windoze to Debian Linux.  And, of course, 
I'm installing things left and right.

I had pgsql up and running, then I had to do a lot of tearing out and 
reinstalling other stuff.  Now, when I try to connect to psql I get:

psql: FATAL:  IDENT authentication failed for user "superbiskit"
OK, so how do I convince the postmaster that I am myself?  Or, how do I 
disarm this form of authentication in favor of plain old password?

Thanks,
--
David A. Cobb, Software Engineer, Public Access Advocate
"By God's Grace, I am a Christian man; by my actions a great sinner." -- The 
Way of a Pilgrim: R.French, Tr.
Life is too short to tolerate crappy software!

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] PRoblems instalation PostgreeSQL8.0.1 in WindowsXP

[Guy Rouillier]

trilcejf wrote:
> Hi there.
> When i try pass the screen "Initialise database cluster" appear this
> message:
> "Tee PostgreeSQL data directory must be on an NTFS formatted volume"
> Any help on this would be appreciated. Thanks in advance 

It means exactly what it says: it wants an NTFS logical partition to
install on.  Apparently your XP Home uses a FAT32 partition, probably
because you upgraded from Win9x.  You should have an accessory to
convert to NTFS.

-- 
Guy Rouillier


---(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] postgres service account

[Jim McMaster]

 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

I found this same problem.  The uninstall really needs to remove this
account.  Either that, or the next install process needs to be able
to get the random password somehow.
 
Open a command prompt, then use the command "net user /delete
postgres" to get rid of the account. "net user" shows all users.
 
- --
Jim McMaster
mailto:[EMAIL PROTECTED]


 



From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Peter
Sent: Friday, March 04, 2005 3:55 PM
To: usya usya; pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgres service account


Why don't you try to use another account for the service - while
reinstalling just fill in 'posgres2' as service account...
 
Best regards,
 
Peter

- Original Message - 
From: usya usya   
To: pgsql-general@postgresql.org 
Sent: Friday, March 04, 2005 4:43 PM
Subject: [GENERAL] postgres service account

hi 
 
I just installed postgres 8.0.1 yesterday on my windows xp machine
and it created a service account with an auto-generated password .
 
pliz help me on how to remove/delete this account. 
 
i tried reinstalling but it couldn't be changed and i couldn't find
it any, where is it stored anyway.




Celebrate Yahoo!'s 10th Birthday! 
Yahoo! Netrospective: 100 Moments of the Web
  


-BEGIN PGP SIGNATURE-
Version: PGP 8.1

iQA/AwUBQijqSGaP5l7/atSJEQLQ8gCfUtEwe91qbHOVn1ZTUJ9h7lgFH6kAoIGy
4mi1prt3QxXKYuQ5jKtiBQDJ
=pUku
-END PGP SIGNATURE-


---(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] postgres service account

[Peter]

Why don't you try to 
use another account for the service - while reinstalling just fill in 'posgres2' 
as service account...
 
Best regards,
 
Peter

  - Original Message - 
  From: 
  usya usya 
  
  To: pgsql-general@postgresql.org 
  
  Sent: Friday, March 04, 2005 4:43 
PM
  Subject: [GENERAL] postgres service 
  account
  
  hi 
   
  I just installed postgres 8.0.1 yesterday on my windows xp machine and it 
  created a service account with an auto-generated password .
   
  pliz help me on how to remove/delete this account. 
   
  i tried reinstalling but it couldn't be changed and i couldn't find it 
  any, where is it stored anyway.
  
  
  Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 
  Moments of the Web 

Re: [GENERAL] Casting from a domain

[Jim C. Nasby]

On Fri, Mar 04, 2005 at 03:40:17PM -0500, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > decibel=# create cast (interval as rrs.seconds) WITH FUNCTION 
> > rrs.interval_to_seconds(interval)  AS IMPLICIT;
> > CREATE CAST
> > decibel=# select cast('1 month'::interval AS seconds);
> > ERROR:  cannot cast type interval to seconds
> 
> Given the current coercion rules, we should probably disallow attempts
> to define casts that involve domains.  Casts are on base types.  The
> down-cast from a domain to its base type is hardwired, and the up-cast
> from base type to domain is too (with invocation of any constraints
> that may apply).  Adding random user-defined casts to this would
> probably just create confusion and ambiguity.  In particular, this
> was already meaningless:
> 
> decibel=# create domain rrs.seconds as double precision;
> CREATE DOMAIN
> decibel=# create cast (double precision as rrs.seconds) WITHOUT FUNCTION AS 
> IMPLICIT;
> CREATE CAST
> 
> since the presence of the cast might be thought to justify coercing
> floats to "seconds" without invoking domain constraints.

Yeah, I figured it was probably an issue with it being a domain. I was
looking for a way to do this without the extra work involved in defining
a full-blown type. I guess the good news is I should be able to re-use
all the double-precision functions and what-not for 'seconds'.

BTW, is there a reason 'double' isn't an alias for 'double precision'?
I'm pretty sure every other database I've used (other than oracle of
course) supports 'double'. " precision" is just too much extra typing
after all... :P
-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] [ADMIN] PostgreSQL installation problem on Windows XP Home

[Bearden Barnes]

See 
the following article in Microsoft's Knowledgebase which describes how to 
get to the system accounts in Windows XP Home.  It specifically addresses 
changing the Windows administrator password, but as long as you follow the 
directions you should see the postgres account along with administrator and 
other system accounts.
 
Cannot Change the 
Password for the Administrator Account in User Accounts in Control 
Panel


  
  
Article ID
:
298252
http://support.microsoft.com/default.aspx?scid=kb;en-us;298252
 
--Bearden Barnes
 

  -Original Message-From: Hagop H. 
  [mailto:[EMAIL PROTECTED]Sent: Friday, March 04, 2005 4:08 
  PMTo: [EMAIL PROTECTED]; [EMAIL PROTECTED]; 
  PGSQL-admin@postgresql.org; PGSQL-general@postgresql.orgSubject: 
  [ADMIN] PostgreSQL installation problem on Windows XP 
Home
  
  I know that my problem is due to 
  my carelessness and ignorance, but I didn't think that installing PostgreSQL 
  would cause a state of affairs not easily reversed. Please excuse the length 
  of the following narrative.
   
  When installing PostgreSQL 8.0.1 
  on my laptop running Windows XP Home (SP2), I let the installer create the 
  postgres account and generate a password. I didn't write down the password. I 
  figured that if the installer was going to generate a random password, it 
  would store it in some configuration or log file and not ask me for it. I was 
  wrong. (Perhaps a warning for idiots like me would be appropriate in the MSI 
  installer file?) When I went to connect to the template1 database, PGAdmin III 
  asked me for the postgres account password, which I was unable to 
  provide.
   
  Normally this wouldn't be a 
  problem. I figured I'd just start up Control Panel and change the postgres 
  account password. Unfortunately, Windows XP Home doesn't provide any obvious 
  tools to change a system account password. I only know how to change user 
  account passwords. Control Panel's User Accounts tool is useless for this 
  problem.
   
  I uninstalled PostgreSQL, thinking 
  that reinstalling it might help. I tried removing the postgres account to no 
  avail. My only indication that the postgres account still exists is the 
  Windows Services administration utility. I know of no method to delete the 
  account. Microsoft's Knowledge Base provided no help. Windows XP Professional 
  contains a tool called Local Users and Groups (lusrmgr.msc) that allows the 
  administrator to change any account's password. Windows XP Home cannot run 
  that program. 
   
  When I reinstall PostgreSQL, I am 
  asked for the postgres account password. I know I could rename the postgres 
  account name to postgres1 or something else, but I don't know if that will 
  create any further issues for me. I could also upgrade the laptop's OS to XP 
  Pro in order to change the postgres account password, but that feels like 
  using a sledgehammer to cut diamonds.
   
  I certainly bear most of the 
  blame. It would be too easy to bash Microsoft for this. I also wish that 
  PostgreSQL's installer did not auto-generate passwords that cannot be changed. 
  None of what I went through was necessary.
   
  If someone could tell me either 
  how to delete the postgres account or change its password, I would appreciate 
  the assistance.
   
  Regards,
   
  Hagop 
  Hagopian

Re: [GENERAL] PRoblems instalation PostgreeSQL8.0.1 in WindowsXP

[Martijn van Oosterhout]

On Wed, Mar 02, 2005 at 11:42:59AM -0500, trilcejf wrote:
> Hi there.
> When i try pass the screen "Initialise database cluster" appear this
> message:
> "Tee PostgreeSQL data directory must be on an NTFS formatted volume"
> Any help on this would be appreciated.
> Thanks in advance

It means exactly what it says. Win98 doesn't support NTFS for example.
Read the FAQ for PostgreSQL on Windows for more details...
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpDePtdtZFE5.pgp
Description: PGP signature

Re: [GENERAL] to_char bug?

[Ben Trewern]

Thanks,  sometimes the obvious just passes me by. :-(

>If the number is negative there needs to be room for the minus sign...

"Martijn van Oosterhout"  wrote in message 
news:[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: [GENERAL] [ADMIN] Database Name

[Dorian Büttner]

Goulet, Dick wrote:
\l works fine in psql, but is there a table where this is stored in the data dictionary? 

-Original Message-
From: Dorian Büttner [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, March 01, 2005 6:16 AM
To: "[EMAIL PROTECTED]"@postgresql.org
Subject: Re: [ADMIN] Database Name

Envbop wrote:

Can someone tell me where I can find the database names.

login using psql and type \l ;-)
chances are you have root access to the linux box, then you could modify 
pg_hba.conf to gain some access

Do you have any account information at all?
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

please stop cross posting
please stop e mailing
please stop tofu
please read the doc 
http://www.postgresql.org/docs/7.4/interactive/managing-databases.html
...

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

[GENERAL] getting attribute names, types

[Surabhi Ahuja]

 i have to write a program in C++ using libpq. The 
program establishes a connection with the desired database.
I have 4 tables in the database. Now i want to get 
the list of attribute names, their data types for each of those 4 tables. how do 
i do that?
 
Thanks you
Surabhi Ahuja

[GENERAL] Running queries or functions stored in a table.

[Matthew Schumacher]

List,
I have a requirement to write a query that lists a set of 
attribute/value pairs, but the values need to be dynamically created on 
the fly from another query or function.  Is it possible to have a table 
like this:

Attribute   |   Value
-
Attrib1 |   select val1, val1 from table;
Attrib2 |   func()
return this:
Attribute   |   Value
-
Attrib1 |   result1
Attrib2 |   result2
If this is possible?  How would I go about it?  I'm thinking pl/pgsql 
and execute() but I'm not sure.

Any thoughts?
schu
---(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] Blob Fields

[Ulrich Schwab]

Alexandre da Siva wrote:

> Blobs is not Implemented on PostgreSQL, but I need to this field type on
> PosgreSQL databases, how I can to use this? I'm using delphi...
> 
> 
> ps: I readed PosgreSQL Manual and other lists and sites, but not get a
> answer for my specific problem
PostgreSQL has large objects, most probably this is what You want.
See this file in the docs:
pgsql/doc/html/largeobjects.html

---(end of broadcast)---
TIP 8: explain analyze is your friend

[GENERAL] PostgreSQL installation problem on Windows XP Home

[Hagop H.]

I know that my problem is due to my carelessness and
ignorance, but I didn’t think that installing PostgreSQL would cause a state
of affairs not easily reversed. Please excuse the length of the following
narrative.

 

When installing PostgreSQL 8.0.1 on my laptop running Windows
XP Home (SP2), I let the installer create the postgres account and generate a password.
I didn’t write down the password. I figured that if the installer was
going to generate a random password, it would store it in some configuration or
log file and not ask me for it. I was wrong. (Perhaps a warning for idiots like
me would be appropriate in the MSI installer file?) When I went to connect to
the template1 database, PGAdmin III asked me for the postgres account password,
which I was unable to provide.

 

Normally this wouldn’t be a problem. I figured I’d
just start up Control Panel and change the postgres account password. Unfortunately,
Windows XP Home doesn’t provide any obvious tools to change a system account
password. I only know how to change user account passwords. Control Panel’s
User Accounts tool is useless for this problem.

 

I uninstalled PostgreSQL, thinking that reinstalling it might
help. I tried removing the postgres account to no avail. My only indication
that the postgres account still exists is the Windows Services administration
utility. I know of no method to delete the account. Microsoft’s Knowledge
Base provided no help. Windows XP Professional contains a tool called Local
Users and Groups (lusrmgr.msc) that allows the administrator to change any
account’s password. Windows XP Home cannot run that program. 

 

When I reinstall PostgreSQL, I am asked for the postgres
account password. I know I could rename the postgres account name to postgres1
or something else, but I don’t know if that will create any further
issues for me. I could also upgrade the laptop’s OS to XP Pro in order to
change the postgres account password, but that feels like using a sledgehammer
to cut diamonds.

 

I certainly bear most of the blame. It would be too easy to
bash Microsoft for this. I also wish that PostgreSQL’s installer did not auto-generate
passwords that cannot be changed. None of what I went through was necessary.

 

If someone could tell me either how to delete the postgres
account or change its password, I would appreciate the assistance.

 

Regards,

 

Hagop Hagopian

[GENERAL] PRoblems instalation PostgreeSQL8.0.1 in WindowsXP

[trilcejf]

Hi there.
When i try pass the screen "Initialise database cluster" appear this
message:
"Tee PostgreeSQL data directory must be on an NTFS formatted volume"
Any help on this would be appreciated.
Thanks in advance


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] postgres service account

[usya usya]

hi 
 
I just installed postgres 8.0.1 yesterday on my windows xp machine and it created a service account with an auto-generated password .
 
pliz help me on how to remove/delete this account. 
 
i tried reinstalling but it couldn't be changed and i couldn't find it any, where is it stored anyway.
		Celebrate Yahoo!'s 10th Birthday!  
Yahoo! Netrospective: 100 Moments of the Web 

[GENERAL] 2147483642::abstime is null, 2147483644::abstime is null

[Filip Jirsák]

Hi,
why this statement:
SELECT  2147483647::abstime, 2147483646::abstime, 2147483645::abstime,
2147483644::abstime, 2147483643::abstime, 2147483642::abstime,
2147483641::abstime, 2147483640::abstime, 2147483639::abstime;

returns null form2147483646 and 2147483645? Is this feature or bug?
(Server version is 7.4.5).
-- 
Filip Jirsák
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] invalid header page in block ....

[PostgreSQL PostgreSQL]

Hello,
I’ve been working with PostgreSQL on Windows for
almost a year. But when I put it in production to the
customer it has had the following error after a power
cut:

“invalid page header in block aaa of relation “bbb”. “

in which aaa is a block number and bbb is a table.
Trying to find out what the problem could be, I could
only realized that it breaks the file corresponding to
the table leaving it in a format that cannot be read
again.
I wasn’t able to reproduce the error in any PC of the
company. At the beginning I thought it could be a
problem from the Cygwin, but I tried the native
version for Windows and the mistake goes on.
I would like to know if it is something related to
PostgreSQL configuration or a mistake.
I’m working with Windows 2000 or Windows XP (and both
get the same problem). I go to the database through
ADO, and I use Delphi as programming language. The
PostgreSQL version is version 8 beta 2 for Windows.
Besides, none of the database where the error appears
is over 300MB.

Thanks, and I look forward to receiving your answer as
soon as possible.


 Expósito, Sergio 
 (Sistem Engineer)
Ábaco Informática S.A.








___ 
250MB gratis, Antivirus y Antispam 
Correo Yahoo!, el mejor correo web del mundo 
http://correo.yahoo.com.ar

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Postgresql driver

[Dorian BÃttner]

Philip Pinkerton wrote:
I am trying to develop an application to access postgresql DB using QT 
developer however when running the developer it cannot find the DB driver?

How can I make the driver available to QT?
Any Ideas would be helpful
Philip

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match
you need to install the qt-psql-plugin
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] Vacuum time degrading

[Wes Palmer]

On 3/2/05 3:51 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote:

> I was going to suggest
> REINDEXing those indexes to see if that cuts the vacuum time at all.

The problem with that is it takes a very long time.  I've got a couple of
things to try yet on the kswapd problem.  If that doesn't work, maybe I can
rebuild one of the indexes and see how much that one improves.  I wasn't
aware that the indexes were scanned non-sequentially.  The under one hour
time was probably shortly after a full reload.  Any chance of change that
behavior to scan in physical storage order?

The index from the largest table that has:

  CPU 216.15s/18.13u sec elapsed 2110.84 sec.

is inserted in sequential order.  The index

  CPU 518.88s/25.17u sec elapsed 10825.33 sec.

has records inserted in essentially a random order, and is also something
like twice as large (key size).

We're going to try to test the 2.4.29 kernel tomorrow.

Wes


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Casting from a domain

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> decibel=# create cast (interval as rrs.seconds) WITH FUNCTION 
> rrs.interval_to_seconds(interval)  AS IMPLICIT;
> CREATE CAST
> decibel=# select cast('1 month'::interval AS seconds);
> ERROR:  cannot cast type interval to seconds

Given the current coercion rules, we should probably disallow attempts
to define casts that involve domains.  Casts are on base types.  The
down-cast from a domain to its base type is hardwired, and the up-cast
from base type to domain is too (with invocation of any constraints
that may apply).  Adding random user-defined casts to this would
probably just create confusion and ambiguity.  In particular, this
was already meaningless:

decibel=# create domain rrs.seconds as double precision;
CREATE DOMAIN
decibel=# create cast (double precision as rrs.seconds) WITHOUT FUNCTION AS 
IMPLICIT;
CREATE CAST

since the presence of the cast might be thought to justify coercing
floats to "seconds" without invoking domain constraints.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Casting from a domain

[Richard Huxton]

Jim C. Nasby wrote:
decibel=# create cast (interval as rrs.seconds) WITH FUNCTION rrs.interval_to_seconds(interval)  AS IMPLICIT;
 ^^^
decibel=# select cast('1 month'::interval AS seconds);
ERROR:  cannot cast type interval to seconds
It's not a schema issue is it? I.E. the lack of "rrs."?
--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] preserving data after updates

[Greg Patnude]

-Original Message-
From: Berend Tober [mailto:[EMAIL PROTECTED] 
Sent: Friday, March 04, 2005 8:47 AM
To: Greg Patnude
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] preserving data after updates

> I use a modified form of option 3 with an ON UPDATE RULE the update
rule
> copies the row to an inherited table...

I just gotta say that THAT is one COOL use of PG inheritance! Do you find
that
it works well and is robust and all the good stuff it seems like would be
the
case?


-- Berend

[GP->] Thank you... ! 

[GP->]  I find it VERY effective and completely transparent to both the
programmer and the end-user... I don't use it on ALL of the tables in a
given schema... ONLY the tables where end-users can manipulate / change
data...
[GP->] 
What it boils down to is that I can use it as a sort of a virtual "rollback"
system by querying the inherited table and updating the parent table with an
original value from the child -- Of course... this results in another change
to the child but it can also be undone... 

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] Casting from a domain

[Jim C. Nasby]

I'm trying to create a seconds domain that is an interval 'mashed to
seconds' as I once saw Tom describe it.

decibel=# create domain rrs.seconds as double precision;
CREATE DOMAIN
decibel=# create cast (double precision as rrs.seconds) WITHOUT FUNCTION AS 
IMPLICIT;
CREATE CAST
decibel=# create function rrs.interval_to_seconds(interval) returns seconds as 
'SELECT extract( EPOCH FROM $1 );' LANGUAGE SQL;
CREATE FUNCTION
decibel=# create cast (interval as rrs.seconds) WITH FUNCTION 
rrs.interval_to_seconds(interval)  AS IMPLICIT;
CREATE CAST
decibel=# select cast('1 month'::interval AS seconds);
ERROR:  cannot cast type interval to seconds

\dC shows that the cast is there, and rrs.interval_to_seconds works as
expected, and according to \df does return seconds.

Version is 7.4.5.
-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] preserving data after updates

[Scott Frankel]

Door number 3.
Thanks for the responses and terrific suggestions!
Scott

On Mar 4, 2005, at 8:28 AM, Greg Patnude wrote:
I use a modified form of option 3 with an ON UPDATE RULE the 
update rule
copies the row to an inherited table...

CREATE TABLE dm_user (
   id SERIAL NOT NULL PRIMARY KEY,
   lu_user_type INTEGER NOT NULL REFERENCES lu_user_type(id),
   dm_user_address INTEGER NOT NULL DEFAULT 0,
   dm_user_email INTEGER NOT NULL DEFAULT 0,
   f_name VARCHAR(50) NOT NULL,
   m_name VARCHAR(50) NOT NULL,
   l_name VARCHAR(50) NOT NULL,
   uname VARCHAR(20) NOT NULL,
   upwd VARCHAR(20) NOT NULL,
   pwd_change_reqd BOOLEAN DEFAULT FALSE,
   login_allowed BOOLEAN DEFAULT TRUE,
   lost_passwd BOOLEAN DEFAULT FALSE,
   create_dt TIMESTAMP NOT NULL DEFAULT NOW(),
   change_dt TIMESTAMP NOT NULL DEFAULT NOW(),
   change_id INTEGER NOT NULL DEFAULT 0,
   active_flag BOOLEAN NOT NULL DEFAULT TRUE
) WITH OIDS;
CREATE TABLE dm_user_history (
   history_id SERIAL NOT NULL PRIMARY KEY,
   hist_create_dt TIMESTAMP NOT NULL DEFAULT NOW()
) INHERITS (dm_user);
CREATE RULE dm_user_upd_history AS ON UPDATE TO dm_user DO INSERT INTO
dm_user_history SELECT * FROM dm_user WHERE id = old.id;
CREATE RULE dm_user_nodelete AS ON DELETE TO dm_user DO INSTEAD UPDATE
dm_user SET active_flag = FALSE WHERE id = old.id;

"Scott Frankel" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
Is there a canonical form that db schema designers use
to save changes to the data in their databases?
For example, given a table with rows of data, if I UPDATE
a field in a row, the previous value is lost.  If I wanted to
track the changes to my data over time, it occurs to me that
I could,
1) copy the whole row of data using the new value, thus
 leaving the old row intact in the db for fishing expeditions,
 posterity, &c.
 -- awfully wasteful, especially with binary data
2) enter a new row that contains only new data fields, requiring
 building a full set of data through heavy lifting and multiple
queries
 through 'n' number of old rows
 -- overly complex query design probably leading to errors
3) create a new table that tracks changes
 -- the table is either wide enough to mirror all columns in
 the working table, or uses generic columns and API tricks to
 parse token pair strings, ...
4) other?
Thanks
Scott
---(end of 
broadcast)---
TIP 4: Don't 'kill -9' the postmaster


---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] preserving data after updates

[Berend Tober]

> I use a modified form of option 3 with an ON UPDATE RULE the update rule
> copies the row to an inherited table...

I just gotta say that THAT is one COOL use of PG inheritance! Do you find that
it works well and is robust and all the good stuff it seems like would be the
case?


-- Berend


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] preserving data after updates

[Greg Patnude]

I use a modified form of option 3 with an ON UPDATE RULE the update rule 
copies the row to an inherited table...

CREATE TABLE dm_user (

   id SERIAL NOT NULL PRIMARY KEY,

   lu_user_type INTEGER NOT NULL REFERENCES lu_user_type(id),
   dm_user_address INTEGER NOT NULL DEFAULT 0,
   dm_user_email INTEGER NOT NULL DEFAULT 0,

   f_name VARCHAR(50) NOT NULL,
   m_name VARCHAR(50) NOT NULL,
   l_name VARCHAR(50) NOT NULL,

   uname VARCHAR(20) NOT NULL,
   upwd VARCHAR(20) NOT NULL,
   pwd_change_reqd BOOLEAN DEFAULT FALSE,
   login_allowed BOOLEAN DEFAULT TRUE,
   lost_passwd BOOLEAN DEFAULT FALSE,

   create_dt TIMESTAMP NOT NULL DEFAULT NOW(),
   change_dt TIMESTAMP NOT NULL DEFAULT NOW(),
   change_id INTEGER NOT NULL DEFAULT 0,
   active_flag BOOLEAN NOT NULL DEFAULT TRUE

) WITH OIDS;


CREATE TABLE dm_user_history (

   history_id SERIAL NOT NULL PRIMARY KEY,
   hist_create_dt TIMESTAMP NOT NULL DEFAULT NOW()

) INHERITS (dm_user);

CREATE RULE dm_user_upd_history AS ON UPDATE TO dm_user DO INSERT INTO 
dm_user_history SELECT * FROM dm_user WHERE id = old.id;

CREATE RULE dm_user_nodelete AS ON DELETE TO dm_user DO INSTEAD UPDATE 
dm_user SET active_flag = FALSE WHERE id = old.id;



"Scott Frankel" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
>
> Is there a canonical form that db schema designers use
> to save changes to the data in their databases?
>
> For example, given a table with rows of data, if I UPDATE
> a field in a row, the previous value is lost.  If I wanted to
> track the changes to my data over time, it occurs to me that
> I could,
>
> 1) copy the whole row of data using the new value, thus
>  leaving the old row intact in the db for fishing expeditions,
>  posterity, &c.
>  -- awfully wasteful, especially with binary data
>
> 2) enter a new row that contains only new data fields, requiring
>  building a full set of data through heavy lifting and multiple 
> queries
>  through 'n' number of old rows
>  -- overly complex query design probably leading to errors
>
> 3) create a new table that tracks changes
>  -- the table is either wide enough to mirror all columns in
>  the working table, or uses generic columns and API tricks to
>  parse token pair strings, ...
>
> 4) other?
>
> Thanks
> Scott
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
> 



---(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] 2147483642::abstime is null, 2147483644::abstime is null

[Tom Lane]

"Dr. Stephane Schildknecht" <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> It doesn't.  It returns "invalid" and "infinity".

> Is there a reason for this ?

I suppose the "invalid" value is a legacy thing, but "infinity"
certainly strikes me as useful.  We have that in the regular
timestamp type as well.

If you're asking why those particular encoded values, I dunno.
Certainly it seems like "infinity" needs to be larger than all
normal values so you don't have to special-case it in comparisons.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] 2147483642::abstime is null, 2147483644::abstime is

[Dr. Stephane Schildknecht]

Tom Lane wrote:
=?ISO-8859-2?Q?Filip_Jirs=E1k?= <[EMAIL PROTECTED]> writes:
why this statement:
SELECT  2147483647::abstime, 2147483646::abstime, 2147483645::abstime,
2147483644::abstime, 2147483643::abstime, 2147483642::abstime,
2147483641::abstime, 2147483640::abstime, 2147483639::abstime;

returns null form2147483646 and 2147483645?

It doesn't.  It returns "invalid" and "infinity".
Is there a reason for this ?
Stéphane
---(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] 2147483642::abstime is null, 2147483644::abstime is null

[Tom Lane]

=?ISO-8859-2?Q?Filip_Jirs=E1k?= <[EMAIL PROTECTED]> writes:
> why this statement:
> SELECT  2147483647::abstime, 2147483646::abstime, 2147483645::abstime,
> 2147483644::abstime, 2147483643::abstime, 2147483642::abstime,
> 2147483641::abstime, 2147483640::abstime, 2147483639::abstime;

> returns null form2147483646 and 2147483645?

It doesn't.  It returns "invalid" and "infinity".

regards, tom lane

---(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] status of inheritance

[Scott Marlowe]

On Fri, 2005-03-04 at 08:41, Joshua N Pritikin wrote:
> I designed a schema which would benefit from inheritance (in particular,
> an index over more than one table).
> 
> However, I read that inheritance is not really implemented yet.  So I
> simulated it with extra indexes and extra joins.
> 
> Is there any plan / timetable for implementing inheritance (with proper
> index support)?

As far as I know, no one is working on that.  It's on the TODO list
though:

http://developer.postgresql.org/todo.php

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] 2147483642::abstime is null, 2147483644::abstime is

[Scott Marlowe]

On Fri, 2005-03-04 at 08:25, Filip JirsÃk wrote:
> Hi,
> why this statement:
> SELECT  2147483647::abstime, 2147483646::abstime, 2147483645::abstime,
> 2147483644::abstime, 2147483643::abstime, 2147483642::abstime,
> 2147483641::abstime, 2147483640::abstime, 2147483639::abstime;
> 
> returns null form2147483646 and 2147483645? Is this feature or bug?
> (Server version is 7.4.5).

I'm running 7.4.7 on FC2, and i get this:

test=# SELECT  2147483647::abstime, 2147483646::abstime,
2147483645::abstime,
test-# 2147483644::abstime, 2147483643::abstime, 2147483642::abstime,
test-# 2147483641::abstime, 2147483640::abstime, 2147483639::abstime;
abstime | abstime |abstime | abstime 
|abstime |abstime   |abstime
|abstime |abstime
+-++--+++++
 2038-01-18 21:14:07-06 | invalid | 2038-01-18 21:14:05-06 | infinity |
2038-01-18 21:14:03-06 | 2038-01-18 21:14:02-06 | 2038-01-18 21:14:01-06
| 2038-01-18 21:14:00-06 | 2038-01-18 21:13:59-06
(1 row)


---(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] status of inheritance

[Joshua N Pritikin]

I designed a schema which would benefit from inheritance (in particular,
an index over more than one table).

However, I read that inheritance is not really implemented yet.  So I
simulated it with extra indexes and extra joins.

Is there any plan / timetable for implementing inheritance (with proper
index support)?



signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] preserving data after updates

[Ian Harding]

I do option 3.  A generic trigger writes the tablename, field name, type
of change (insert/update/delete) and the old and new values for columns
that were affected.  It is kind of a hog, but it works very well.  I
have cron delete old entries so it doesn't eat my whole disk. 

I haven't tried to get it to give up the data in the same representation
as the source table, but it shouldn't be too hard with a set returning
function, such that you tell it the table name and timestamp and it
returns records as they existed at that time.  I usually just query it
directly to "see what happened".


>>> Scott Frankel <[EMAIL PROTECTED]> 03/03/05 3:51 PM >>>

Is there a canonical form that db schema designers use
to save changes to the data in their databases?

For example, given a table with rows of data, if I UPDATE
a field in a row, the previous value is lost.  If I wanted to
track the changes to my data over time, it occurs to me that
I could,

1) copy the whole row of data using the new value, thus
  leaving the old row intact in the db for fishing expeditions,
  posterity, &c.
  -- awfully wasteful, especially with binary data

2) enter a new row that contains only new data fields, requiring
  building a full set of data through heavy lifting and multiple 
queries
  through 'n' number of old rows
  -- overly complex query design probably leading to errors

3) create a new table that tracks changes
  -- the table is either wide enough to mirror all columns in
  the working table, or uses generic columns and API tricks to
  parse token pair strings, ...

4) other?

Thanks
Scott


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


---(end of broadcast)---
TIP 8: explain analyze is your friend

[GENERAL] 2147483642::abstime is null, 2147483644::abstime is null

[Filip Jirsák]

Hi,
why this statement:
SELECT  2147483647::abstime, 2147483646::abstime, 2147483645::abstime,
2147483644::abstime, 2147483643::abstime, 2147483642::abstime,
2147483641::abstime, 2147483640::abstime, 2147483639::abstime;
returns null form2147483646 and 2147483645? Is this feature or bug?
(Server version is 7.4.5).
Thanks.
Filip Jirsák
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] Blob Fields

[J. Greenlees]

Alexandre da Siva wrote:
Blobs is not Implemented on PostgreSQL, but I need to this field type on 
PosgreSQL databases, how I can to use this?
I'm using delphi...
ps: I readed PosgreSQL Manual and other lists and sites, but not get a 
answer for my specific problem
http://www.postgresql.org/docs/8.0/interactive/datatype-binary.html
definitions for blob, with usage.


smime.p7s
Description: S/MIME Cryptographic Signature

[GENERAL] Blob Fields

[Alexandre da Siva]

Blobs is not Implemented on PostgreSQL, 
but I need to this field type on 
PosgreSQL databases, how I can to use this?
I'm using delphi...
 
 
ps: I readed PosgreSQL Manual and other lists and 
sites, but not get a answer for my specific problem