[GENERAL] UUID's as primary keys

[Thomas Hallgren]

I'm building an app where I have several entities that are identified 
using a UUID (i.e. a 128 bit quantity). My current implementation uses a 
composite primary key consisting of two int8 values. It's a bit 
cumbersome and I would much rather have a distinct type. An earlier 
implementation using Oracle mapped the UUID to a RAW(16) but PostgreSQL 
doesn't have that and the BYTEA adds extra overhead.


What would be the best (as in most efficient) mapping for a 128 bit 
primary key?


Regards,
Thomas Hallgren


---(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

Re: [GENERAL] UUID's as primary keys

[Martijn van Oosterhout]

On Wed, Jun 28, 2006 at 09:01:49AM +0200, Thomas Hallgren wrote:
> I'm building an app where I have several entities that are identified 
> using a UUID (i.e. a 128 bit quantity). My current implementation uses a 
> composite primary key consisting of two int8 values. It's a bit 
> cumbersome and I would much rather have a distinct type. An earlier 
> implementation using Oracle mapped the UUID to a RAW(16) but PostgreSQL 
> doesn't have that and the BYTEA adds extra overhead.
> 
> What would be the best (as in most efficient) mapping for a 128 bit 
> primary key?

Sounds like something for a custom type. There's one here[1] though I
have no idea how good it is.

[1] http://gborg.postgresql.org/project/pguuid/projdisplay.php

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

[GENERAL] How can I speed up this query?

[Pat Maddox]

I end up calling this query quite often, so I need a way to speed it
up.  Don't know if I can take advantage of indexes or better joins or
something (I don't even know if there are better joins), I'm really a
newbie to DB stuff.  Hoping somebody can help me make this much
quicker.


EXPLAIN ANALYZE SELECT count(r) FROM trainer_hand_results r,
trainer_scenarios s, trainer_scenario_stats stats WHERE r.user_id=1
and r.trainer_scenario_id=s.id AND s.id=stats.trainer_scenario_id AND
r.action=stats.correct_action AND r.created_at >= '2006-06-07
11:21:22' AND s.hero_position='BB';

Aggregate  (cost=36.29..36.30 rows=1 width=32) (actual
time=545.484..545.485 rows=1 loops=1)
  ->  Nested Loop  (cost=0.00..36.29 rows=1 width=32) (actual
time=544.177..545.292 rows=6 loops=1)
->  Merge Join  (cost=0.00..30.23 rows=2 width=40) (actual
time=424.720..471.695 rows=60 loops=1)
  Merge Cond: ("outer".trainer_scenario_id =
"inner".trainer_scenario_id)
  Join Filter: (("outer"."action")::text =
("inner".correct_action)::text)
  ->  Index Scan using
trainer_hand_results_trainer_scenario_id_index on trainer_hand_results
r  (cost=0.00..19.12 rows=570 width=44) (actual time=415.677..420.197
rows=578 loops=1)
Filter: ((user_id = 1) AND (created_at >=
'2006-06-07 11:21:22'::timestamp without time zone))
  ->  Index Scan using
trainer_scenario_stats_trainer_scenario_id_index on
trainer_scenario_stats stats  (cost=0.00..26.58 rows=117 width=12)
(actual time=8.835..49.954 rows=185 loops=1)
->  Index Scan using trainer_scenarios_pkey on
trainer_scenarios s  (cost=0.00..3.02 rows=1 width=4) (actual
time=1.222..1.222 rows=0 loops=60)
  Index Cond: ("outer".trainer_scenario_id = s.id)
  Filter: ((hero_position)::text = 'BB'::text)
Total runtime: 546.082 ms
(12 rows)

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

  http://archives.postgresql.org

Re: [GENERAL] How can I speed up this query?

[Martijn van Oosterhout]

On Wed, Jun 28, 2006 at 01:52:10AM -0600, Pat Maddox wrote:
> I end up calling this query quite often, so I need a way to speed it
> up.  Don't know if I can take advantage of indexes or better joins or
> something (I don't even know if there are better joins), I'm really a
> newbie to DB stuff.  Hoping somebody can help me make this much
> quicker.


>   ->  Index Scan using
> trainer_hand_results_trainer_scenario_id_index on trainer_hand_results
> r  (cost=0.00..19.12 rows=570 width=44) (actual time=415.677..420.197
> rows=578 loops=1)

That's an awful long time to find a few rows in a table via an index.
How big is this table/index?

What version is this? If you have a older version perhaps you're
suffering from index bloat and you need to REINDEX that table/index.

Hope this helps,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] UUID's as primary keys

[Thomas Hallgren]

Martijn van Oosterhout wrote:

On Wed, Jun 28, 2006 at 09:01:49AM +0200, Thomas Hallgren wrote:
  
I'm building an app where I have several entities that are identified 
using a UUID (i.e. a 128 bit quantity). My current implementation uses a 
composite primary key consisting of two int8 values. It's a bit 
cumbersome and I would much rather have a distinct type. An earlier 
implementation using Oracle mapped the UUID to a RAW(16) but PostgreSQL 
doesn't have that and the BYTEA adds extra overhead.


What would be the best (as in most efficient) mapping for a 128 bit 
primary key?



Sounds like something for a custom type. There's one here[1] though I
have no idea how good it is.

[1] http://gborg.postgresql.org/project/pguuid/projdisplay.php

Have a nice day,
  
Thanks. That would of course work but at the same time it increases the 
complexity of my app. Yet another component to install and keep track 
of. It's also a bit of an overkill since the only thing I need is an 
opaque bit storage. Why is it that PostgreSQL lack a fixed length binary 
type similar to the RAW type in Oracle? ISTM that could be very useful 
and not very hard to implement.


Regards,
Thomas Hallgren


---(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

Re: [GENERAL] UUID's as primary keys

[Martijn van Oosterhout]

On Wed, Jun 28, 2006 at 10:22:45AM +0200, Thomas Hallgren wrote:
> Thanks. That would of course work but at the same time it increases the 
> complexity of my app. Yet another component to install and keep track 
> of. It's also a bit of an overkill since the only thing I need is an 
> opaque bit storage. Why is it that PostgreSQL lack a fixed length binary 
> type similar to the RAW type in Oracle? ISTM that could be very useful 
> and not very hard to implement.

AIUI, it can't be done because of a basic rule of the type system: the
typmod can't be necessary to interpret the binary representation of a
value. For something like RAW(16) the type would be the oid for "raw"
and the typmod would be 16. However, when reading the value from a disk
page, you're not given the typmod, so you have no way of determining
the length.

That's why there is a verlena header for types where you don't know the
length, which adds overhead. The alternative is to create a type of the
exact length you want, but in your case that's not acceptable either.

I'm not sure if there are any other alternatives.

Have a ncie day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] RAID + PostgreSQL?

[Franz . Rasper]

How much I/O Performance do you need ?

READ Performance ? Write Performance ?

I need an fast and reliable RAID Controller (harddisks have to be hot plug,
automatic rebuild etc.)
and I have to say that the HP DL 380 G 4 with Battery Backup Write Cache ,
FAST U320 HDs,
some gigs ram (chipkill is a nice feature), redundant fans and power
supplies is a good database server.
Unfortunately I had no chance to test the HP DL 385. 

Greetings,

-Franz

-Ursprüngliche Nachricht-
Von: Scott Marlowe [mailto:[EMAIL PROTECTED] 
Gesendet: Dienstag, 27. Juni 2006 17:45
An: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; pgsql general
Betreff: Re: [GENERAL] RAID + PostgreSQL?


On Tue, 2006-06-27 at 08:05, [EMAIL PROTECTED] wrote:
> I have here HP DL 380 G3 und HP DL 380 G4. (here Smart Array 5i and Smart
> Array 6i)
> Maybe there are problems with old linux kernel (but as far as i know >
> 2.4.27)
> I dont know about such performance problems, maybe the conrollers are
faster
> under windows. 
> 
> The battery backup write cache are very import for inserts.
> 
> You should use for performance test with dd under linux larger blocksizes,
> but you should compare postgresql under linux and windows (selects,
update,
> inserts, etc.)

Note that having the latest driver usually helps a lot too.

The LSI megaraid was solid but a mediocre performer with the older 1.18
driver, but the 2.x series driver was much faster when I tested it.

---(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

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

Re: [GENERAL] UUID's as primary keys

[Thomas Hallgren]

Martijn van Oosterhout wrote:

On Wed, Jun 28, 2006 at 10:22:45AM +0200, Thomas Hallgren wrote:
Thanks. That would of course work but at the same time it increases the 
complexity of my app. Yet another component to install and keep track 
of. It's also a bit of an overkill since the only thing I need is an 
opaque bit storage. Why is it that PostgreSQL lack a fixed length binary 
type similar to the RAW type in Oracle? ISTM that could be very useful 
and not very hard to implement.


AIUI, it can't be done because of a basic rule of the type system: the
typmod can't be necessary to interpret the binary representation of a
value. For something like RAW(16) the type would be the oid for "raw"
and the typmod would be 16. However, when reading the value from a disk
page, you're not given the typmod, so you have no way of determining
the length.

OK. I thought you always had a type descriptor handy when reading the binary representation. 
I've noticed that the typmod is expected in some receive functions (bpcharrecv and 
numeric_recv for instance). Are you saying that there are times when you don't use that?


Regards,
Thomas Hallgren


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

Re: [GENERAL] Question concerning arrays

[Karsten Hilbert]

On Tue, Jun 27, 2006 at 01:43:21PM +0200, Christian Rengstl wrote:

> i am in the middle of breaking my head over designing a
> database and came to the following question/problem: i have
> persons whose values (integer) have to be entered in the db,
> but per person the amount of values ranges from 10  to
> around 50. Now my question is if it makes sense, concerning
> performance, to store these integer values in arrays or if
> it is better to change the design so that the values are
> stored separately in fields in tables. I have to add, that
> it is not unlikely (or at least it won't happen very often)
> that select queries will try to find one of those specific
> values, but rather something like select * from persons
> where person_id=...
If each value has a distinct medical meaning (think blood
sugar readings) which makes sense on its own apart from all
the other values it should very likely be stored as a
separate value in the database.

If, however, it is just one integer in a large batch of them
(think raw values of EEG readings) which only really make
sense in conjunction with each other and even then mainly to
the application they should probably be stored as arrays or
some other aggregate datatype. You might still want to query
for single values out of the batch at times - detecting
Ausreißer, fitting curves, detecting peaks of activity, etc.

Karsten

Assistenzarzt für Kinderheilkunde,
Chirurgie und Allgemeinmedizin
Leipzig
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

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

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

Re: [GENERAL] UUID's as primary keys

[Martijn van Oosterhout]

On Wed, Jun 28, 2006 at 12:03:40PM +0200, Thomas Hallgren wrote:
> OK. I thought you always had a type descriptor handy when reading the 
> binary representation. I've noticed that the typmod is expected in some 
> receive functions (bpcharrecv and numeric_recv for instance). Are you 
> saying that there are times when you don't use that?

The input functions get it, the output functions (bpcharout,
bpcharsend, etc) don't. Which makes it kind of hard to print a raw
value if you don't know how long it's going to be. They used to, but
that was removed some time back. It's a security issue IIRC, since any
user could call raw_out(field, 2048) and get whatever was in the 2K of
data after that field.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

[GENERAL] invalid primary checkpoint record

[zhicheng wang]

dear all 

we have the following issues and cannot start the
postgresql server:

postgresql 8.0.5
redhat EL WS4 U3

the computer was shutdown properly. then the user
restarted the computer, in the middle of rebooting, he
switched off the power button on the wall. after that
we cannot start the database server any more. the
following is the log. please help.

thanks
cheng


LOG:  database system shutdown was interrupted at
2006-06-26 10:33:11 BST
LOG:  could not open file
"/var/lib/pgsql/data/pg_xlog/0001"
(log file 0, segment 0): No such file or directory
LOG:  invalid primary checkpoint record
LOG:  could not open file
"/var/lib/pgsql/data/pg_xlog/0001"
(log file 0, segment 0): No such file or directory
LOG:  invalid secondary checkpoint record
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 29959) was terminated by
signal 6
LOG:  aborting startup due to startup process failure
LOG:  logger shutting down
LOG:  database system shutdown was interrupted at
2006-06-26 10:33:11 BST
LOG:  could not open file
"/var/lib/pgsql/data/pg_xlog/0001"
(log file 0, segment 0): No such file or directory
LOG:  invalid primary checkpoint record
LOG:  could not open file
"/var/lib/pgsql/data/pg_xlog/0001"
(log file 0, segment 0): No such file or directory
LOG:  invalid secondary checkpoint record
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 30015) was terminated by
signal 6
LOG:  aborting startup due to startup process failure
LOG:  logger shutting down


Euro-China Medlink ltd
http://www.euro-china.co.uk



___ 
Now you can scan emails quickly with a reading pane. Get the new Yahoo! Mail. 
http://uk.docs.yahoo.com/nowyoucan.html

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

Re: [GENERAL] UUID's as primary keys

[Thomas Hallgren]

Martijn van Oosterhout wrote:

On Wed, Jun 28, 2006 at 12:03:40PM +0200, Thomas Hallgren wrote:
  
OK. I thought you always had a type descriptor handy when reading the 
binary representation. I've noticed that the typmod is expected in some 
receive functions (bpcharrecv and numeric_recv for instance). Are you 
saying that there are times when you don't use that?



The input functions get it, the output functions (bpcharout,
bpcharsend, etc) don't. Which makes it kind of hard to print a raw
value if you don't know how long it's going to be. They used to, but
that was removed some time back. It's a security issue IIRC, since any
user could call raw_out(field, 2048) and get whatever was in the 2K of
data after that field.

  
A user that is trusted with installing a C-function in the backend is 
free to scan the process memory anyway so in what way did that increase 
the security? IMHO, the only relevant security in that context is to 
have trusted people install trusted modules. I'm surprised that 
something like that made you remove significant functionality.


Regards,
Thomas Hallgren


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

[GENERAL] Null and Void() - Or, Abandon All Hope Ye Who allow NULLs?

[dananrg]

Date and Pascal hate nulls. One even goes so far as to say that if you
permit NULLs in a database, then the results from *every* query is
suspect. So they turn perform backflips suggesting ways to avoid nulls.
None, so far, seem appealing.

To me, nulls are quite useful in the Real World. For instance, there
may be a lot of immediate value for end users in committing a row that
has a few nulls (e.g. as in not yet collected), rather than slavishly
follow a rule that says Thou Shalt Not commit a row with nulls.

Can't the intelligent practitioner simply proceed carefully with
queries when nulls are involved? With group functions at least, I
believe nulls are ignored. In Oracle, you can use NVL() to force group
functions to recognize.

What about simply doing an IS NULL test in the code when any table that
allows nulls in involved in a query?

What precisely has Date and Pascal's knickers in such a twist? The fact
that ad hoc queries from random, unintelligent users could give strange
results?

What if one has control over the queries performed through a GUI or
application? Doesn't the problem disappear, presuming the programmer is
aware of the issue and the application is well documented?

What are some of the best ways to deal with the issue of nulls
potentially resulting in questionable query results short of
disallowing them? Storing some sort of coded or numeric value that
represents UNKNOWN or NOT APPLICABLE.


---(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

Re: [GENERAL] Null and Void() - Or, Abandon All Hope Ye Who allow NULLs?

[dananrg]

'Scuse my broken english and ungrammatical gibberish in my last
missive. That's what I get for posting before my first cup of coffee
for the day.

I'm serious about nulls though, and am wondering if Date and Pascal,
perhaps, are the gibberish ones on this particular issue. The
insistence on no nulls, anywhere, any time, for any reason, seems--from
a practical vantage--unreasonable.


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

   http://archives.postgresql.org

Re: [GENERAL] psql -h host ...

[James]

Thanks all for the reply.
Once pg_hba.conf is modified, do I have to run
pg_ctl reload ?

James

louis gonzales wrote:
> James,
> the "psql" command as you know is just the command line program that
> requests connection to a database and depending how you issue the
> command, determines if it's attempting to connect to a local file, or
> via a network protocol (commonly TCP/IP).  When you issue the command
> from a remote host, relative to where the database is located, once the
> database server receives the connection request, that request is looked
> up in the pg_hba.conf file to validate the user attempting the request.
>
> If the user meets the constraints imposed within, the user is granted
> access to the database, otherwise, "try again."
>
> If you launch the psql command directly on the server to connect locally
> to the database, in this scenario, the server is 'the client' and
> therefore would be looking up pg_hba.conf on 'the client' but this case
> is no different from the remote client to the server, in that there are
> still both roles being fulfilled and ultimately 'the server' is looking
> up the request in the pg_hba.conf.
>
> Hope this helps.
>
> Martijn van Oosterhout wrote:
>
> >On Mon, Jun 26, 2006 at 01:51:24PM -0700, James wrote:
> >
> >
> >>In this command
> >>psql -h host ...
> >>
> >>does it look for pg_hba.conf in the client or in the server?
> >>
> >>
> >
> >The server. Client machines do not necessarily have a pg_hba.conf and
> >even if they do, clients are unlikely to be able to read it.
> >
> >Have a nice day,
> >
> >
>
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq


---(end of broadcast)---
TIP 1: 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] error while installing the 8.1.4 rpms on my m/c

[surabhi.ahuja]

Title: Re: [GENERAL] planning to upgrade to 8.1






i have 4 rpms 

i first install 
compat-postgresql-libs-3-3PGDG.i686.rpm
then i install
postgresql-libs-8.1.4-1PGDG.i686.rpm
 
the i install
postgresql-8.1.4-1PGDG.i686.rpm
 
here i get an error it says
 
failed dependency
librealdline.so.5 is needed by 
postgresql-8.1.4-1PGDG.i686.rpm
 
what do i do?
thanks,
regards
Surabhi



From: Devrim GUNDUZ 
[mailto:[EMAIL PROTECTED]Sent: Tue 6/27/2006 10:09 
PMTo: surabhi.ahujaCc: 
pgsql-general@postgresql.orgSubject: Re: [GENERAL] planning to 
upgrade to 8.1

Hi,On Tue, 2006-06-27 at 20:49 +0530, surabhi.ahuja 
wrote:> it is ok if i uninstall the current version of postgres 
8.0.0No. Please read this before you proceed:http://pgfoundry.org/docman/?group_id=148>  
i am trying to install the rpms for 8.1.4 FC(4) Where can i find the> 
instaructions to download the rpms. http://www.PostgreSQL.org/ftp/binary/v8.1.4/linux/rpms/fedora/fedora-core-4> 
what i did was as follows,   1. instal the rpm> 
postgresql-libs-8.1.4-1PGDG.i686.rpm 2. install the rpm> 
postgresql-libs-8.1.4-1PGDG.i686.rpm   however this step was 
giving> errors, some dependency errorsI think you'll need this 
RPM also:http://developer.postgresql.org/~devrim/rpms/compat/compat-postgresql-libs-3-3PGDG.i686.rpmto 
satisfy some libpq dependencies.Regards,--The PostgreSQL Company 
- Command Prompt, Inc. 1.503.667.4564PostgreSQL Replication, Consulting, 
Custom Development, 24x7 supportManaged Services, Shared and Dedicated 
HostingCo-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Re: [GENERAL] Null and Void() - Or, Abandon All Hope Ye Who allow

[Florian G. Pflug]

[EMAIL PROTECTED] wrote:

Date and Pascal hate nulls. One even goes so far as to say that if you
permit NULLs in a database, then the results from *every* query is
suspect. So they turn perform backflips suggesting ways to avoid nulls.
None, so far, seem appealing.

To me, nulls are quite useful in the Real World. For instance, there
may be a lot of immediate value for end users in committing a row that
has a few nulls (e.g. as in not yet collected), rather than slavishly
follow a rule that says Thou Shalt Not commit a row with nulls.

Can't the intelligent practitioner simply proceed carefully with
queries when nulls are involved? With group functions at least, I
believe nulls are ignored. In Oracle, you can use NVL() to force group
functions to recognize.

What about simply doing an IS NULL test in the code when any table that
allows nulls in involved in a query?

What precisely has Date and Pascal's knickers in such a twist? The fact
that ad hoc queries from random, unintelligent users could give strange
results?

What if one has control over the queries performed through a GUI or
application? Doesn't the problem disappear, presuming the programmer is
aware of the issue and the application is well documented?

What are some of the best ways to deal with the issue of nulls
potentially resulting in questionable query results short of
disallowing them? Storing some sort of coded or numeric value that
represents UNKNOWN or NOT APPLICABLE.

I'd say the "problem" with NULL values is mainly that they conflict with
some of the ideas of relational theory and relational algebra.
One of the basic ideas of relation theory is that of functional 
dependencies. Database normalization (at least according to the theory)

tells you how to deduce a "good" schema, if you know what columns you
need, and what their functional dependencies are.

Of course, the functions described by those functional dependencies are 
not required to be defined for every possible value - let's say you have
a function dependency A -> B - meaning that whenever you know the value 
of column A, then there is _at_most_ one value for column BNormalization 
basically tells you to model that function dependency as a

table containing fields A and B, and make A the primary key.

Now, if there is no B for a specific value of A, then this table will 
just not contain a record for this value of A. But if you allow 
NULL-values, then suddently there are _two_ different ways to express

"I don't know what B is for this A". You could either have a record with
the A-value in question, and with B null, or you could have _no_ record
with the A-value in question.

So, NULLs IMHO give you flexibility - but at a price. The  price is that 
the _same_ information could be stored in different ways - and you can't

really deduce "the correct way" from the schema alone.

For me, the discussion is quite similar to static-typed vs. dynamically 
typed languages - the first ones enable the compiler to check your code 
more thoroughly, while the second ones often allow you to write more 
concise code.


So, I'd say Date and Pascal are right from a _theoretical_ point of view 
- null values really cause problems in the context of relational theory.
Those theoretical problems in turn cause practical problems to some 
extent - but so do a _lot_ of other things. Take java as an example -

at least until java 1.4 this language has huge theoretical deficiencies
in it's type system, but you can nevertheless write good and 
maintainable code in java 1.4. The same is true for SQL with nulls - 
there _are_ possibilities to shoot yourself in the foot, but that just

means that developers need to be skilled enough to know about those
pitfalls.


greetings, Florian Pflug

---(end of broadcast)---
TIP 1: 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 while installing the 8.1.4 rpms on my m/c

[Devrim GUNDUZ]

Hi,

On Wed, 2006-06-28 at 18:20 +0530, surabhi.ahuja wrote:
> failed dependency librealdline.so.5 is needed by
> postgresql-8.1.4-1PGDG.i686.rpm   

yum install readline

Regards,
-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/



---(end of broadcast)---
TIP 1: 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] Null and Void() - Or, Abandon All Hope Ye Who allow

[Berend Tober]

Florian G. Pflug wrote:
> [EMAIL PROTECTED] wrote:
>
>> Date and Pascal hate nulls.
>
> ...the functions described by those functional dependencies are
> not required to be defined for every possible value - let's say you have
> a function dependency A -> B - meaning that whenever you know the value
> of column A, then there is _at_most_ one value for column BNormalization
> basically tells you to model that function dependency as a
> table containing fields A and B, and make A the primary key.
>
> Now, if there is no B for a specific value of A, then this table will
> just not contain a record for this value of A. But if you allow
> NULL-values, then suddently there are _two_ different ways to express
> "I don't know what B is for this A". You could either have a record with
> the A-value in question, and with B null, or you could have _no_ record
> with the A-value in question.
>

But in the former case, you affirm the existence and your knowledge of 
the second A-value; in the latter case you affirm ignorance of the 
second A-value. The two-column example may be useful for theoretical 
discussion, but in practise likely more columns exist so that NULL can 
represent incomplete data that may be determined later for a particular 
row when you still need to commit the column values already known. For 
instance, in response to customer demands, it may be required that a new 
employee begins work on projects right away, even though we have only 
basic identifying information, like say, their name. This gives us 
enough to create a new employee row, start recording their labor hours 
worked for billing purposes, and to cut checks for travel expenses. We 
eventually need date of birth, social security number, and other 
information, but as a practical matter those columns can certainly be 
committed NULL initially.


Regards,
Berend Tober
860-767-0700 x118

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

Re: [GENERAL] Null and Void() - Or, Abandon All Hope Ye Who allow

[Florian G. Pflug]

Berend Tober wrote:

Florian G. Pflug wrote:
 > [EMAIL PROTECTED] wrote:
 >
 >> Date and Pascal hate nulls.
 >
 > ...the functions described by those functional dependencies are
 > not required to be defined for every possible value - let's say you have
 > a function dependency A -> B - meaning that whenever you know the value
 > of column A, then there is _at_most_ one value for column BNormalization
 > basically tells you to model that function dependency as a
 > table containing fields A and B, and make A the primary key.
 >
 > Now, if there is no B for a specific value of A, then this table will
 > just not contain a record for this value of A. But if you allow
 > NULL-values, then suddently there are _two_ different ways to express
 > "I don't know what B is for this A". You could either have a record with
 > the A-value in question, and with B null, or you could have _no_ record
 > with the A-value in question.
 >


But in the former case, you affirm the existence and your knowledge of 
the second A-value; in the latter case you affirm ignorance of the 
second A-value. The two-column example may be useful for theoretical 
discussion, but in practise likely more columns exist so that NULL can 
represent incomplete data that may be determined later for a particular 
row when you still need to commit the column values already known.

I came up with the two-column example because it's the simplest example
possible. For larger tables you _could_ split them into n tables (at 
most one per field). If not saying I'd do that - just that it's possible

and that it's basically what Date and Pascal suggest.

> For
instance, in response to customer demands, it may be required that a new 
employee begins work on projects right away, even though we have only 
basic identifying information, like say, their name. This gives us 
enough to create a new employee row, start recording their labor hours 
worked for billing purposes, and to cut checks for travel expenses. We 
eventually need date of birth, social security number, and other 
information, but as a practical matter those columns can certainly be 
committed NULL initially.

Well, yes - as I said, using null values gives you more flexibility. But
still, you _can_ shoot yourself in the foot by using them - that's why
it's still good to know why some people oppose them, even if you don't
share their point of view. But of course, "rm -r $(PGDATA)" is a more 
efficient way to shoot yourself in the foot, and will probably harm more

then using null ;-)

greetings, Florian Pflug

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

Re: [GENERAL] Null and Void() - Or, Abandon All Hope Ye Who allow

[Andrew Gould]

--- Berend Tober <[EMAIL PROTECTED]> wrote:

> Florian G. Pflug wrote:
>  > [EMAIL PROTECTED] wrote:
>  >
>  >> Date and Pascal hate nulls.
>  >
>  > ...the functions described by those functional
> dependencies are
>  > not required to be defined for every possible
> value - let's say you have
>  > a function dependency A -> B - meaning that
> whenever you know the value
>  > of column A, then there is _at_most_ one value
> for column BNormalization
>  > basically tells you to model that function
> dependency as a
>  > table containing fields A and B, and make A the
> primary key.
>  >
>  > Now, if there is no B for a specific value of A,
> then this table will
>  > just not contain a record for this value of A.
> But if you allow
>  > NULL-values, then suddently there are _two_
> different ways to express
>  > "I don't know what B is for this A". You could
> either have a record with
>  > the A-value in question, and with B null, or you
> could have _no_ record
>  > with the A-value in question.
>  >
> 
> But in the former case, you affirm the existence and
> your knowledge of 
> the second A-value; in the latter case you affirm
> ignorance of the 
> second A-value. The two-column example may be useful
> for theoretical 
> discussion, but in practise likely more columns
> exist so that NULL can 
> represent incomplete data that may be determined
> later for a particular 
> row when you still need to commit the column values
> already known. For 
> instance, in response to customer demands, it may be
> required that a new 
> employee begins work on projects right away, even
> though we have only 
> basic identifying information, like say, their name.
> This gives us 
> enough to create a new employee row, start recording
> their labor hours 
> worked for billing purposes, and to cut checks for
> travel expenses. We 
> eventually need date of birth, social security
> number, and other 
> information, but as a practical matter those columns
> can certainly be 
> committed NULL initially.
> 
> Regards,
> Berend Tober
> 860-767-0700 x118
> 

Null values should be allowed for any information that
may not be known at the time of data entry.  However,
any data field that falls into this category should
not be required to define the relationships between
tables.  This is a case where the database design must
reflect the limitations of operational processes.

Andrew Gould



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

Re: [GENERAL] UUID's as primary keys

[Martijn van Oosterhout]

On Wed, Jun 28, 2006 at 01:56:47PM +0200, Thomas Hallgren wrote:
> A user that is trusted with installing a C-function in the backend is 
> free to scan the process memory anyway so in what way did that increase 
> the security? IMHO, the only relevant security in that context is to 
> have trusted people install trusted modules. I'm surprised that 
> something like that made you remove significant functionality.

You're missing the point. The type output function is not generally a
priveledged function. Think bpcharout, text_out, numeric_out, etc...
These can be called by users directly and the input to those functions
cannot be trusted.

If the type output function needs an additional parameter to correctly
and safely decode the actual Datum, you're screwed because then users
can pass invalid parameters to affect the decoding. If you have a way
of telling what the right value is, then you didn't need to pass it in
the first place.

Hence, you have to be able to decode a datum knowing only its type,
irrespective of typmod. So say you had a field of type RAW(16) you
would have to be able to decode it knowing only that it is of type
"RAW". So you need a header to tell you how long it is i.e. a varlena
structure.

Hope this clarifies it a bit,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Null and Void() - Or, Abandon All Hope Ye Who allow

[Andrew Gould]

--- Andrew Gould <[EMAIL PROTECTED]> wrote:

> --- Berend Tober <[EMAIL PROTECTED]> wrote:
> 
> > Florian G. Pflug wrote:
> >  > [EMAIL PROTECTED] wrote:
> >  >
> >  >> Date and Pascal hate nulls.
> >  >
> >  > ...the functions described by those functional
> > dependencies are
> >  > not required to be defined for every possible
> > value - let's say you have
> >  > a function dependency A -> B - meaning that
> > whenever you know the value
> >  > of column A, then there is _at_most_ one value
> > for column BNormalization
> >  > basically tells you to model that function
> > dependency as a
> >  > table containing fields A and B, and make A the
> > primary key.
> >  >
> >  > Now, if there is no B for a specific value of
> A,
> > then this table will
> >  > just not contain a record for this value of A.
> > But if you allow
> >  > NULL-values, then suddently there are _two_
> > different ways to express
> >  > "I don't know what B is for this A". You could
> > either have a record with
> >  > the A-value in question, and with B null, or
> you
> > could have _no_ record
> >  > with the A-value in question.
> >  >
> > 
> > But in the former case, you affirm the existence
> and
> > your knowledge of 
> > the second A-value; in the latter case you affirm
> > ignorance of the 
> > second A-value. The two-column example may be
> useful
> > for theoretical 
> > discussion, but in practise likely more columns
> > exist so that NULL can 
> > represent incomplete data that may be determined
> > later for a particular 
> > row when you still need to commit the column
> values
> > already known. For 
> > instance, in response to customer demands, it may
> be
> > required that a new 
> > employee begins work on projects right away, even
> > though we have only 
> > basic identifying information, like say, their
> name.
> > This gives us 
> > enough to create a new employee row, start
> recording
> > their labor hours 
> > worked for billing purposes, and to cut checks for
> > travel expenses. We 
> > eventually need date of birth, social security
> > number, and other 
> > information, but as a practical matter those
> columns
> > can certainly be 
> > committed NULL initially.
> > 
> > Regards,
> > Berend Tober
> > 860-767-0700 x118
> > 
> 
> Null values should be allowed for any information
> that
> may not be known at the time of data entry. 
> However,
> any data field that falls into this category should
> not be required to define the relationships between
> tables.  This is a case where the database design
> must
> reflect the limitations of operational processes.
> 
> Andrew Gould
>

I need to temper my own response.  I was referring to
relationships between tables where both tables contain
operational data.  The use of reference tables, such
as code lookup tables, is a huge exception to my
comment.

Andrew Gould

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

   http://archives.postgresql.org

Re: [GENERAL] UUID's as primary keys

[Tom Lane]

Martijn van Oosterhout  writes:
> The input functions get it, the output functions (bpcharout,
> bpcharsend, etc) don't. Which makes it kind of hard to print a raw
> value if you don't know how long it's going to be. They used to, but
> that was removed some time back.

Even back then you couldn't rely on the typmod value to be supplied;
it was quite likely to be passed as -1.  The issue is not actually
with on-disk storage, it is with function/operator arguments and
results.  Those have never been identified any more closely than by
giving a type OID.  So for any value that came from a function,
you won't have a typmod, and you'd better be able to find out all
you need to know just by inspecting the value itself.  Hence, length
words.

This is all pretty off-topic for pgsql-general, isn't it?

regards, tom lane

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

Re: [GENERAL] Null and Void() - Or,

[A.M.]

On Wed, June 28, 2006 5:31 am, [EMAIL PROTECTED] wrote:
> Date and Pascal hate nulls. One even goes so far as to say that if you
> permit NULLs in a database, then the results from *every* query is suspect.
> So they turn perform backflips suggesting ways to avoid nulls.
> None, so far, seem appealing.

This has been discussed to death on this list and on every other SQL
forum, but since you asked...

To understand NULL, there is a little history that needs to be brought up.
The original relational model proposal by Codd had no provisions for
non-existent data. Mathematical purity is a strong argument against NULL.
Another one is just as simple: "NULL represents the absence of data, so it
is the antithesis of what should be stored in a _data_base."

In Codd's later papers, he comes up with several distinct NULLs
representing different states of unknowledge. Date is vehemently opposed
to NULL for the aforementioned reasons.

NULL is nothing more than a shortcut. SQL logic has to do backflips to
accomodate it- notice how NULL!=NULL- indeed, one NULL can mean a variety
of things even in the same context! ("Bob doesn't know","HR doesn't
know","No one cares","Not applicable", etc.)

In this paper:
http://web.onetel.com/~hughdarwen/TheThirdManifesto/Missing-info-without-nulls.pdf
Darwen discusses relational design without NULLs (his solution requires
support for distributed keys which PostgreSQL admittedly doesn't support)
but the premise is very simply that data can be partitioned so that the
lack of knowledge is implicit in its absence (which is part of the
relational model- it should be a closed system of truths).

Obviously, for practical purposes, NULL isn't going anywhere fast for SQL
databases, but it is really good to know the background and rationale for
your own and other's design decisions.

I hope this has helped.

-M



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

Re: [GENERAL] invalid primary checkpoint record

[Tom Lane]

zhicheng wang <[EMAIL PROTECTED]> writes:
> the computer was shutdown properly. then the user
> restarted the computer, in the middle of rebooting, he
> switched off the power button on the wall. after that
> we cannot start the database server any more. the
> following is the log. please help.

You can probably get out of this with pg_resetxlog, but after that you
need to take a hard look at that machine's disk drive setup.  It's
evidently lying about write-complete (ie, not doing writes in the
commanded order), else you could not have got into this state.

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] Null and Void() - Or,

[Martijn van Oosterhout]

On Wed, Jun 28, 2006 at 10:43:26AM -0400, A.M. wrote:
> NULL is nothing more than a shortcut. SQL logic has to do backflips to
> accomodate it- notice how NULL!=NULL- indeed, one NULL can mean a variety
> of things even in the same context! ("Bob doesn't know","HR doesn't
> know","No one cares","Not applicable", etc.)

Err, NULL!=NULL => NULL, NULL=NULL => NULL. Comparing NULL with
anything produces NULL, which is neither true nor false. It's straight
three-valued logic. If anything the odd thing is that NULL is
interpreted as false in some contexts.

Yes, there are some places in SQL that need to do strange things with
NULL, but this isn't one of them.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Null and Void() - Or, Abandon All Hope Ye Who allow NULLs?

[David Fetter]

On Wed, Jun 28, 2006 at 02:31:20AM -0700, [EMAIL PROTECTED] wrote:
> Date and Pascal hate nulls.  One even goes so far as to say that if
> you permit NULLs in a database, then the results from *every* query
> is suspect.  So they turn perform backflips suggesting ways to avoid
> nulls.  None, so far, seem appealing.

That is part of the story.  Interestingly to me is that the more
stridently they've rejected NULLs, the fuzzier their suggestions have
been about what to do in cases of missing information.

[snip]

> What precisely has Date and Pascal's knickers in such a twist?  The
> fact that ad hoc queries from random, unintelligent users could give
> strange results?

What has their knickers in a twist is what twists the knickers of
every other doctrinaire ideologue: that everybody doesn't "just get"
why their way is far superior, drop everything they're doing, and
change over to the True Way™.  Their "Eat Grapefruit, You Morons"
 tactics don't do anything to
endear them either.

It's good to read what Date, Darwen & Pascal have to say, but only
once so you can recognize the flavor of bamboozlement when some
impressionable youth (of whatever age) has a "revelation" about How
Databases Should Be®.  This way, you can help explain gently that
their "new insight" is neither new nor insightful, and that there are
some good papers  to
read.

Cheers,
D
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

---(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

Re: [GENERAL] Null and Void() - Or, Abandon All Hope Ye Who allow

[Tim Hart]

This reminds me of my favorite Bertrand Russell quote:

"The difference between theory and practice is: in theory there is no
difference, but in practice, there is"

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Florian G. Pflug
Sent: Wednesday, June 28, 2006 8:45 AM
To: Berend Tober
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Null and Void() - Or, Abandon All Hope Ye Who allow

Well, yes - as I said, using null values gives you more flexibility. But
still, you _can_ shoot yourself in the foot by using them - that's why
it's still good to know why some people oppose them, even if you don't
share their point of view. But of course, "rm -r $(PGDATA)" is a more 
efficient way to shoot yourself in the foot, and will probably harm more
then using null ;-)

greetings, Florian Pflug

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



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

Re: [GENERAL] Null and Void() - Or, Abandon All Hope Ye Who allow NULLs?

[David Fetter]

On Wed, Jun 28, 2006 at 02:43:03AM -0700, [EMAIL PROTECTED] wrote:
> 'Scuse my broken english and ungrammatical gibberish in my last
> missive.  That's what I get for posting before my first cup of
> coffee for the day.
> 
> I'm serious about nulls though, and am wondering if Date and Pascal,
> perhaps, are the gibberish ones on this particular issue.

Most people, on listening to a string of nonsense, will tend to
doubt their own sanity before they realize that the person who is
jabbering at them is really the one with the damaged brain.
   Neal Stephenson, The Big U

> The insistence on no nulls, anywhere, any time, for any reason,
> seems--from a practical vantage--unreasonable.

You're right.  It is :)

Cheers,
D
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

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

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

[GENERAL] empty text fields

[Leif B. Kristensen]

On a tangent to the never-ending NULL debate, I've got a table:

CREATE TABLE events (
event_idINTEGER PRIMARY KEY,
tag_fk  INTEGER REFERENCES tags (tag_id),
place_fkINTEGER REFERENCES places (place_id),
event_date  CHAR(18) NOT NULL DEFAULT '31',
sort_date   DATE NOT NULL DEFAULT '40041024BC',
event_note  TEXT NOT NULL DEFAULT ''
);

The event_note will contain text in roughly 1 out of 5 rows:

pgslekt=> select count(*) from events;
 count
---
 29473
(1 row)

pgslekt=> select count(*) from events where event_note <> '';
 count
---
  5572
(1 row)

I wonder if this is sane design, in theory and in practice, or should I 
break out the event_note field in a separate table?
-- 
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE

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

Re: [GENERAL] empty text fields

[David Fetter]

On Wed, Jun 28, 2006 at 05:14:42PM +0200, Leif B. Kristensen wrote:
> On a tangent to the never-ending NULL debate, I've got a table:
> 
> CREATE TABLE events (
> event_idINTEGER PRIMARY KEY,
> tag_fk  INTEGER REFERENCES tags (tag_id),
> place_fkINTEGER REFERENCES places (place_id),
> event_date  CHAR(18) NOT NULL DEFAULT '31',
  
This is Bad™.  What's wrong with TIMESTAMPTZ?

> sort_date   DATE NOT NULL DEFAULT '40041024BC',
> event_note  TEXT NOT NULL DEFAULT ''

This is generally bad, too.  It's got MySQL goo all over it.  Do you
want NOT NULL, or do you want a DEFAULT that's meaningful?

> );
> 
> The event_note will contain text in roughly 1 out of 5 rows:
> 
> pgslekt=> select count(*) from events;
>  count
> ---
>  29473
> (1 row)
> 
> pgslekt=> select count(*) from events where event_note <> '';
>  count
> ---
>   5572
> (1 row)
> 
> I wonder if this is sane design, in theory and in practice, or should I 
> break out the event_note field in a separate table?

Only if it's a 1:N relationship.  In this case, I'd say scrap the NOT
NULL requirement and replace the empty strings with NULLs.

Cheers,
D
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

---(end of broadcast)---
TIP 1: 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] Idea for vacuuming

[Jim C. Nasby]

On Fri, Jun 23, 2006 at 02:00:38PM -0400, Joseph Shraibman wrote:
> I like to make sure the vacuum takes place during off peak times, which 
> is why I don't use autovacuum.

FWIW, now that there's vacuum_cost_delay that's usually not a very good
strategy. If you have anywhere close to enough load during they day that
runing vacuum would impact things then you're going to end up with
tables and indexes that are horribly bloated because they're not being
vacuumed enough. You'll probably get better overall performance by using
autovacuum and setting vacuum_cost_delay to about 20, depending on your
hardware.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

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

Re: [GENERAL] oh dear - have i messed up my OIDs?

[Jim C. Nasby]

On Sun, Jun 25, 2006 at 03:14:41PM -0400, brian ally wrote:
> My question is, can i safely run my modifications again, but swap out 
> the INSERTs for the COPY WITH OIDs bit from the dump (adding my new 
> INSERTs after)? Would those OIDs still be "good", so to speak?
 
Probably.

> ANY advice appreciated.

Don't use OIDs, use SERIALs instead. You're going to run into all kinds
of problems using OIDs.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(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] unusual "relation xxx does not exist" problem

[Bill Eaton]

I'm trying to migrate a database cluster from one server to another. So I
did a pg_dumpall, ported everything over, yada yada yada.

I have one particular schema in one particular database that is giving me
trouble. All names for tables, sequences, functions, etc. are lowercase. I
repeat: everything is lowercase.

I'm using pgadmin iii to look at everything and I continually get "relation
not found" errors with my "failurelog" table and other tables. I've tried
DROP CASCADEing the schema and database and then rebuilding one table at a
time, but it just is not working.

I've also done another experiment where I create a brand new table that
never existed. I still get "relation not found" errors on that table. The
only common thread seems to be the schema.

The name of the schema is
   bar

"bar" is not a reserved word in PostgreSQL, according to the documentation.
Is there any corrupted information that might be stored in the templates
(template0 template1) that would account for this problem? Could it be a bug
in pgadmin?

Bill Eaton
Southern California



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

   http://archives.postgresql.org

Re: [GENERAL] empty text fields

[Leif B. Kristensen]

On Wednesday 28. June 2006 17:37, David Fetter wrote:
>On Wed, Jun 28, 2006 at 05:14:42PM +0200, Leif B. Kristensen wrote:
>> On a tangent to the never-ending NULL debate, I've got a table:
>>
>> CREATE TABLE events (
>> event_idINTEGER PRIMARY KEY,
>> tag_fk  INTEGER REFERENCES tags (tag_id),
>> place_fkINTEGER REFERENCES places (place_id),
>> event_date  CHAR(18) NOT NULL DEFAULT
>> '31',
>
>  
>This is Bad™.  What's wrong with TIMESTAMPTZ?

What's Bad™?

The event_date field is a "fuzzy date" construct. It will allow the 
storage of such dates as "1784", "ca. 1810", "May 1852", "1798 or 
1799", "between 1820 and 1830" and so on. It's very useful in 
historical research to handle such dates meaningfully.

By the way, I was also going to ask sometime if there's a better way to 
handle such a construct than an unspecified CHAR(18) column.

>> sort_date   DATE NOT NULL DEFAULT '40041024BC',
>> event_note  TEXT NOT NULL DEFAULT ''
>
>This is generally bad, too.  It's got MySQL goo all over it.  Do you
>want NOT NULL, or do you want a DEFAULT that's meaningful?

Sure. I came to PostgreSQL about a year ago. I've been using MySQL for 
read-only data since 2002, but when I started writing my own data-entry 
routines, I found the "quiet truncation" misfeature of MySQL to render 
it all but useless.

>> I wonder if this is sane design, in theory and in practice, or
>> should I break out the event_note field in a separate table?
>
>Only if it's a 1:N relationship.  In this case, I'd say scrap the NOT
>NULL requirement and replace the empty strings with NULLs.

Thanks. I'll think it over.
-- 
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE

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

   http://archives.postgresql.org

Re: [GENERAL] unusual "relation xxx does not exist" problem

[Jim C. Nasby]

On Wed, Jun 28, 2006 at 09:02:04AM -0700, Bill Eaton wrote:
> I'm trying to migrate a database cluster from one server to another. So I
> did a pg_dumpall, ported everything over, yada yada yada.
> 
> I have one particular schema in one particular database that is giving me
> trouble. All names for tables, sequences, functions, etc. are lowercase. I
> repeat: everything is lowercase.
> 
> I'm using pgadmin iii to look at everything and I continually get "relation
> not found" errors with my "failurelog" table and other tables. I've tried
> DROP CASCADEing the schema and database and then rebuilding one table at a
> time, but it just is not working.
> 
> I've also done another experiment where I create a brand new table that
> never existed. I still get "relation not found" errors on that table. The
> only common thread seems to be the schema.
> 
> The name of the schema is
>bar
> 
> "bar" is not a reserved word in PostgreSQL, according to the documentation.
> Is there any corrupted information that might be stored in the templates
> (template0 template1) that would account for this problem? Could it be a bug
> in pgadmin?

Possibly. Can you reproduce it in psql? Keep in mind you'll need to
either specify the schema name or ensure that bar is in search_path.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

Re: [GENERAL] unusual "relation xxx does not exist" problem

[Bill Eaton]

> > I'm trying to migrate a database cluster from one server to
> another. So I
> > did a pg_dumpall, ported everything over, yada yada yada.
> >
> > I have one particular schema in one particular database that is
> giving me
> > trouble. All names for tables, sequences, functions, etc. are
> lowercase. I
> > repeat: everything is lowercase.
> >
> > I'm using pgadmin iii to look at everything and I continually
> get "relation
> > not found" errors with my "failurelog" table and other tables.
> I've tried
> > DROP CASCADEing the schema and database and then rebuilding one
> table at a
> > time, but it just is not working.
> >
> > I've also done another experiment where I create a brand new table that
> > never existed. I still get "relation not found" errors on that
> table. The
> > only common thread seems to be the schema.
> >
> > The name of the schema is
> >bar
> >
> > "bar" is not a reserved word in PostgreSQL, according to the
> documentation.
> > Is there any corrupted information that might be stored in the templates
> > (template0 template1) that would account for this problem?
> Could it be a bug
> > in pgadmin?
>
> Possibly. Can you reproduce it in psql? Keep in mind you'll need to
> either specify the schema name or ensure that bar is in search_path.

Oops. That's a detail I left out. I can only reproduce this problem so far
in PGAdmin. I couldn't reproduce the error in psql or in a linked table in
MSAccess. This is what leads me to wonder if a system table is screwed up.
PGAdmin must be looking at system tables behind the scenes to figure out all
of the info on tables and field names, etc.

Since dropping the schema, the database, and even the "postgres" maintenance
database doesn't fix the problem, I was wondering if something could be
frelled up in template0 or template1. And I'm scared to mess with those.
Though if there is a safe way to regenerate those, I'm perfectly willing to
nuke them, too.

-Bill Eaton




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

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

[GENERAL] Strange Behavior with Serializable Transcations

[Brad Nicholson]

I'm seeing something fairly unintuitive about serializable transactions.

Taking the following test case:

CREATE TABLE foo (id integer);

t1   t2
--   BEGIN;
--   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO foo (id) --
VALUES (1);  --
--   SELECT * from foo;

The select in t2 sees the row inserted from t1, which it shouldn't.

http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html

"When a transaction is on the serializable level, a SELECT query sees
only data committed before the transaction began; it never sees either
uncommitted data or changes committed during transaction execution by
concurrent transactions."

Now, if I modify the case as such:

t1   t2
--   BEGIN;
--   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--   SELECT * from foo;
INSERT INTO foo (id) --
VALUES (1);  --
--   SELECT * from foo;

The select in t2 (the last one, obviously) does not see the insert from t1.

What's up?


-- 
Brad Nicholson  416-673-4106[EMAIL PROTECTED]
Database Administrator, Afilias Canada Corp.


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

   http://archives.postgresql.org

[GENERAL] Is it possible to disable insert/update/delete triggers for one transaction and not another?

[Karen Hill]

I have an insert/update/delete trigger on all my tables which add data
to a log table.

I would like to be able to disable them when the tables are called from
one stored proceedure I have.  Yet I would still like those triggers to
fire on any other operation that is happening concurrently.  Is this
even possible?


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

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

Re: [GENERAL] Strange Behavior with Serializable Transcations

[Martijn van Oosterhout]

On Wed, Jun 28, 2006 at 02:48:01PM -0400, Brad Nicholson wrote:
> I'm seeing something fairly unintuitive about serializable transactions.
> 
> Taking the following test case:



> http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html
> 
> "When a transaction is on the serializable level, a SELECT query sees
> only data committed before the transaction began; it never sees either
> uncommitted data or changes committed during transaction execution by
> concurrent transactions."

I think the issue here is that transaction begin is not when you type
"begin" but at your first actual query. You can obviously only start a
transaction once you know what serialisation level you want, and you
don't see that till after the begin.

Hope this helps,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] empty text fields

[Karsten Hilbert]

On Wed, Jun 28, 2006 at 06:25:22PM +0200, Leif B. Kristensen wrote:

> >> event_date  CHAR(18) NOT NULL DEFAULT

> The event_date field is a "fuzzy date" construct. It will allow the 
> storage of such dates as "1784", "ca. 1810", "May 1852", "1798 or 
> 1799", "between 1820 and 1830" and so on. It's very useful in 
> historical research to handle such dates meaningfully.
How and where do you handle the fuzziness of it ? In the
application ? We have to deal with the same thing in medical
history data and haven't yet satisfactorily solved it.

> By the way, I was also going to ask sometime if there's a better way to 
> handle such a construct than an unspecified CHAR(18) column.

A composite type comes to mind. Tagged types (google for
"tagged_type") would probably help, too.

A "full-blown" implementation of a fuzzy timestamp type which

a) preserves the input/update timestamp
b) allows setting the accuracy of the value per row
c) allows for known "modifiers" and "terms" ("mid-summer", "second half of ...")
d) allows for an arbitrary textual addition ("ca.", "probably")

would be great. I know I should be helping to write one
instead of hoping someone does it for me. But I lack the
expertise to do it properly. I am willing to help, at any
rate, though.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

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

Re: [GENERAL] empty text fields

[Karsten Hilbert]

self-replying with additional information

On Wed, Jun 28, 2006 at 09:33:18PM +0200, Karsten Hilbert wrote:

> A "full-blown" implementation of a fuzzy timestamp type which
> 
> a) preserves the input/update timestamp
which tagged_types is able to handle

> b) allows setting the accuracy of the value per row
which I have a Python (application) class for wrapping mx.DateTime.DateTime

> c) allows for known "modifiers" and "terms" ("mid-summer", "second half of 
> ...")

> d) allows for an arbitrary textual addition ("ca.", "probably")
which tagged_types should be able to handle as well

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

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

Re: [GENERAL] Strange Behavior with Serializable Transcations

[Bruno Wolff III]

On Wed, Jun 28, 2006 at 14:48:01 -0400,
  Brad Nicholson <[EMAIL PROTECTED]> wrote:
> I'm seeing something fairly unintuitive about serializable transactions.
> 
> "When a transaction is on the serializable level, a SELECT query sees
> only data committed before the transaction began; it never sees either
> uncommitted data or changes committed during transaction execution by
> concurrent transactions."

This has come up on the lists before. Take a look at:
http://secure.linuxports.com/pgsql-admin/2006-01/msg00256.php

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

Re: [GENERAL] empty text fields

[Leif B. Kristensen]

On Wednesday 28. June 2006 21:33, Karsten Hilbert wrote:
>On Wed, Jun 28, 2006 at 06:25:22PM +0200, Leif B. Kristensen wrote:
>> >> event_date  CHAR(18) NOT NULL DEFAULT
>>
>> The event_date field is a "fuzzy date" construct. It will allow the
>> storage of such dates as "1784", "ca. 1810", "May 1852", "1798 or
>> 1799", "between 1820 and 1830" and so on. It's very useful in
>> historical research to handle such dates meaningfully.
>
>How and where do you handle the fuzziness of it ? In the
>application ? We have to deal with the same thing in medical
>history data and haven't yet satisfactorily solved it.

For now, I'm handling it in the application. But as I'm very much into 
moving as much as possible into views, functions and triggers of the 
db, I'm investigating how to do it in a more efficient way.

The construct has been lifted pretty much unmodified from "The Master 
Genealogist" (TMG), the genealogy program I was using until I decided 
to write my own. The format of the string is MMDDAMMDDB. The 
YYYMMDD is a "normal" date, where the day may be set to 00 if the month 
is known, and the month likewise set to 00 if only the year is known. 
The A is a qualifier, which can take the following values:

 0 = before (date1),
 1 = say (date1),
 2 = ca. (date1),
 3 = exact (date1),
 4 = after (date1),
 5 = between (date1) and (date2),
 6 = (date1) or (date2),
 7 = from (date1) to (date2).

The B can be either 0 for BC or 1 for AD.

>> By the way, I was also going to ask sometime if there's a better way
>> to handle such a construct than an unspecified CHAR(18) column.
>
>A composite type comes to mind. Tagged types (google for
>"tagged_type") would probably help, too.
>
>A "full-blown" implementation of a fuzzy timestamp type which
>
>a) preserves the input/update timestamp
>b) allows setting the accuracy of the value per row
>c) allows for known "modifiers" and "terms" ("mid-summer", "second
> half of ...") d) allows for an arbitrary textual addition ("ca.",
> "probably")
>
>would be great. I know I should be helping to write one
>instead of hoping someone does it for me. But I lack the
>expertise to do it properly. I am willing to help, at any
>rate, though.

I haven't felt any need to enter more irregular dates than those 
described above. I think that if a date is that much undetermined, it 
would be better to render it textually in a free-text note.
-- 
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE

---(end of broadcast)---
TIP 1: 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] unusual "relation xxx does not exist" problem

[Tom Lane]

"Bill Eaton" <[EMAIL PROTECTED]> writes:
> Oops. That's a detail I left out. I can only reproduce this problem so far
> in PGAdmin. I couldn't reproduce the error in psql or in a linked table in
> MSAccess.

Turn on query logging so you can see exactly what query PGAdmin is
issuing to provoke the error.  There isn't any magic in PGAdmin, it
has to issue the same queries as anyone else.

regards, tom lane

---(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

Re: [GENERAL] Strange Behavior with Serializable Transcations

[Tom Lane]

Martijn van Oosterhout  writes:
> I think the issue here is that transaction begin is not when you type
> "begin" but at your first actual query. You can obviously only start a
> transaction once you know what serialisation level you want, and you
> don't see that till after the begin.

Right, the snapshot does not become set until you do a non-utility
command (normally, SELECT/INSERT/UPDATE/DELETE).  This is a feature, not
a bug, because it lets the transaction take table locks before its
snapshot becomes set.  I believe the Fine Manual's discussion of
concurrency includes some examples where such locks are needed ...

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] unusual "relation xxx does not exist" problem

[Bill Eaton]

> > Oops. That's a detail I left out. I can only reproduce this
> > problem so far
> > in PGAdmin. I couldn't reproduce the error in psql or in a
> > linked table in
> > MSAccess.
>
> Turn on query logging so you can see exactly what query PGAdmin is
> issuing to provoke the error.  There isn't any magic in PGAdmin, it
> has to issue the same queries as anyone else.
>
>   regards, tom lane

Tom, you're a genius. There was some strangeness with the search path for
the user I was connected as. The displayed search path was
  "bar,beam,truss"
but the search path should really have been
   bar, beam, truss

Once I changed that and reconnected to the database, everything went back to
what I expected. I couldn't have sorted it all out without the logging,
which I have since turned off.

-Bill Eaton




---(end of broadcast)---
TIP 1: 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