pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: dev@archonet.com
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Date Validation?
Date: Sat, 14 Jul 2001 16:29:57 +0100 (BST)
Message-ID: <1239.192.168.1.16.995124597.squirrel@mainbox.archonet.com> (raw)
In-Reply-To: <web-85443@davinci.ethosmedia.com>
References: <web-85443@davinci.ethosmedia.com>

> Richard,
> 
>> Out of curiosity Josh, why aren't you validating in PHP? - only takes
>> a
>> couple of lines there.
> 
> We are.  It's just that all other validation takes place inside the
> PL/pgSQL functions in order to keep all the business logic in the
> database.  Having one form of validation in the interface (PHP) and the
> rest in the function can cause the user to go through multiple
> validation errors, which is especially frustrating if the second
> validation error is fatal.  Example:
[snip]

Hmm - fair enough - PITA really.

Poked around and had a think and can't come up with anything better than
Stephan Szabo's suggestion of hacking PG's built-in function. You'll want
to_timestamp() which is called from to_date and it's in
src/backend/utils/adt/formatting.c - AFAICT you'll just need to comment out
the elog(...) calls and return a null value (or whatever).

Looks like there are a lot of dependencies - my C is too rusty to figure
that out just by skimming. I'm guessing the validation is fiddly though -
PHP's date handling code is no shorter.

Failing that, what about writing is_valid_date() in pl-tcl/perl? Don't know
anything about tcl, but should be easy enough in perl (cut & paste from a
suitable module)

Or (and this is horrible) check the date in PHP and if it's not valid
replace it with NULL. You can check for the null in the trigger fn, but
unfortunately you no longer have the original value for your error message
(no, I don't like it either).

For the interested on the list:
The central problem seems to be that the error logging function elog() never
returns from an ERROR message and kills the whole transaction.
This makes sense since any code can call elog(ERROR,...) and not have to
worry about recovering from the error.
If PostgreSQL had been written using Java, there'd probably be try...catch
everywhere and it wouldn't be an issue (of course there might well be
performance problems as well as someone having to invent java 10 years
before Sun did ;-)

Presumably, once we have nested transactions all this will be magically
solved by wrapping the possibly dodgy statements with an inner transaction.

Aside: I may have found the world's first "Y2K BC" bug - if we make enough
noise over this it could turn the IT industry round again.

richardh=> select '01-01-01 BC'::date, '0001-01-01 BC'::date;
   ?column?    |   ?column?
---------------+---------------
 2001-01-01 BC | 0001-01-01 BC
(1 row)

- Richard Huxton




view thread (8+ messages)  latest in thread

Message-ID: <1239.192.168.1.16.995124597.squirrel@mainbox.archonet.com>
Permalink:  ../1239.192.168.1.16.995124597.squirrel@mainbox.archonet.com/
Also on:    postgresql.org/message-id/1239.192.168.1.16.995124597.squirrel@mainbox.archonet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: dev@archonet.com, josh@agliodbs.com
  Subject: Re: Date Validation?
  In-Reply-To: <1239.192.168.1.16.995124597.squirrel@mainbox.archonet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox