Jeff Boes | 1 Oct 15:28 2004

Re: psql variable interpolation from command line

Ugh, never mind. I finally saw the reason in the 'psql' documentation. Missed it
the first time. (And the second, and third, ...)

--

-- 
Jeff Boes                                  vox 269.226.9550 ext 24
Database Engineer                                 fax 269.349.9076
Nexcerpt, Inc.                             http://www.nexcerpt.com
          ...Nexcerpt... Extend your Expertise

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

Jeff Boes | 1 Oct 15:24 2004

psql variable interpolation from command line

I'm at a loss to explain this one:

$ psql -v AUTHOR="'foo'" -c 'select :AUTHOR;'
ERROR:  syntax error at or near ":" at character 8

$ psql -v AUTHOR="'foo'"
...
# select :AUTHOR;
 ?column?
----------
 foo
(1 row)

In other words, why won't variable interpolation work when the "-c" flag is
used?

--

-- 
Jeff Boes                                  vox 269.226.9550 ext 24
Database Engineer                                 fax 269.349.9076
Nexcerpt, Inc.                             http://www.nexcerpt.com
          ...Nexcerpt... Extend your Expertise

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

               http://archives.postgresql.org

Bruno Wolff III | 1 Oct 20:28 2004
Picon

Re: date_trunc'd timestamp index possible?

On Mon, Sep 27, 2004 at 19:14:09 -0500,
  "D. Duccini" <duccini <at> backpack.com> wrote:
> 
> I'm trying to create a index from a timestamp+tz field and want the index
> to be date_trunc'd down to just the date
> 
> when i try to do a
> 
> create idxfoo on foo (date(footime));
> 
> i get a 
> 
> ERROR:  DefineIndex: index function must be marked IMMUTABLE
> 
> and it chokes on when i try to use the date_trunc() function as well
> 
> create idxfoo on foo (date_trunc('day',footime));
> 
> ERROR:  parser: parse error at or near "'day'" at character 53
> 
> Any suggestions/workarounds (other than creating additional date-only
> columns in the schema and indexing those???)

The reason this doesn't work is that the timestamp to date conversion
depends on the time zone setting. In theory you should be able to avoid
this by specifying the time zone to check the date in. I tried something
like the following which I think should work, but doesn't:
create idxfoo on foo (date(timezone('UTC',footime)));

The conversion of the timestamp stored in footime should be immutable
(Continue reading)

Bruno Wolff III | 1 Oct 20:44 2004
Picon

Re: date_trunc'd timestamp index possible?

On Fri, Oct 01, 2004 at 13:28:30 -0500,
  Bruno Wolff III <bruno <at> wolff.to> wrote:
> 
> I am not sure how to check if the supplied function for converting
> a timestamp with time zone to a timestamp without timezone using a
> specified time zone is immutable. I think this function should be
> immutable, but that it probably isn't.

I found that most of the various timezone functions are marked as stable
instead of immutable. I think at least a couple of these should be
marked as immutable and I will try reporting this as a bug.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo <at> postgresql.org so that your
      message can get through to the mailing list cleanly

Tom Lane | 1 Oct 20:49 2004
Picon

Re: [SQL] date_trunc'd timestamp index possible?

Bruno Wolff III <bruno <at> wolff.to> writes:
> I am not sure how to check if the supplied function for converting
> a timestamp with time zone to a timestamp without timezone using a
> specified time zone is immutable. I think this function should be
> immutable, but that it probably isn't.

Yup.  In 7.4:

regression=# select provolatile from pg_proc where oid = 'timezone(text,timestamptz)'::regprocedure;
 provolatile
-------------
 s
(1 row)

regression=#

This is a thinko that's already been corrected for 8.0:

regression=# select provolatile from pg_proc where oid = 'timezone(text,timestamptz)'::regprocedure;
 provolatile
-------------
 i
(1 row)

regression=#

If you wanted you could just UPDATE pg_proc to correct this mistake.
Another possibility is to create a function that's an IMMUTABLE
wrapper around the standard function.

(Continue reading)

D. Duccini | 1 Oct 20:28 2004

Re: date_trunc'd timestamp index possible?


> The reason this doesn't work is that the timestamp to date conversion
> depends on the time zone setting. In theory you should be able to avoid
> this by specifying the time zone to check the date in. I tried something
> like the following which I think should work, but doesn't:
> create idxfoo on foo (date(timezone('UTC',footime)));
> 
> The conversion of the timestamp stored in footime should be immutable
> and then taking the date should work. I did find that date of a timestamp
> without time zone is treated as immutable.
> 
> I am not sure how to check if the supplied function for converting
> a timestamp with time zone to a timestamp without timezone using a
> specified time zone is immutable. I think this function should be
> immutable, but that it probably isn't.

I think we found a way around it!

CREATE OR REPLACE FUNCTION date_immutable( timestamptz ) RETURNS date AS
'SELECT date( $1 ) ;' LANGUAGE 'sql' IMMUTABLE ;

CREATE INDEX "new_event_dt" ON "the_events" USING btree (
date_immutable( "event_dt_tm" ) ) ;

-----------------------------------------------------------------------------
david <at> backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.    
+1 651.645.9798 fax            Don't forget your BackPack!"   
-----------------------------------------------------------------------------

(Continue reading)

Tom Lane | 1 Oct 23:17 2004
Picon

Re: date_trunc'd timestamp index possible?

"D. Duccini" <duccini <at> backpack.com> writes:
> I think we found a way around it!

> CREATE OR REPLACE FUNCTION date_immutable( timestamptz ) RETURNS date AS
> 'SELECT date( $1 ) ;' LANGUAGE 'sql' IMMUTABLE ;

No, you just found a way to corrupt your index.  Pretending that
date(timestamptz) is immutable does not make it so.  The above
*will* break the first time someone uses the table with a different
timezone setting.

What you can do safely is date(footime AT TIME ZONE 'something'),
since this nails down the zone in which the date is interpreted.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo <at> postgresql.org so that your
      message can get through to the mailing list cleanly

Dag Gullberg | 3 Oct 12:58 2004
Picon

Concurrency problem

Hi,

	I have a problem with concurrency,
where ordinary selects render me

Warning: pg_query(): Query failed: ERROR: tuple concurrently updated
CONTEXT: PL/pgSQL function "get_rights" line 5 at SQL statement in
/home/site/PHP/db_func.php on line 301

Code of get_rights:
	GRANT SELECT ON c.users TO
	adm,w3;                                                                                                                                        
        SELECT rights INTO result
                FROM c.users
                WHERE usr_id=uid;

        REVOKE ALL ON c.users FROM w3,adm;
        RETURN result;

What function is in error at a specific point in time appears to 
be random. There are no "update" SQL commands issued between two loads
(actually two meny selections in sequence, the first not being served
completely). Still the db complain about "updates". I use triggers only
at "create" and in some instances "update", not when doing "select".
Tables are interconnected by foreign keys, sometimes mutliple.

Is there anybody out there with similar experiences? 
Someone who might point in some directions in terms of 
documentation of *how* to design and use a database to 
avoid concurrency problems?
(Continue reading)

Marcin Piotr Grondecki | 1 Oct 18:46 2004
Picon

Re: SELECT with Function


Paulo Nievierowski wrote:
| PS: Excuses my poor english.
Your english is excellent. MY is poor!!

| I create plpgsql function "myfunc(integer)" thats returns of type
| record with values from table X (the function decides what record must
| be select). The parameter is an keyprod existing in table X and in
| table A.
|
| It run well sending in psql:
| SELECT * FROM myfunc( 10006530 ) as (vret1 numeric, vret2 numeric);
|
| The problem is:
| I need return data from table A (this have column 'keyprod'). This
| value (keyprod) must be passed as parameter to myfunc(). The result
| must be a union of columns selected from table A and result of
| myfunc().
|
| How to create this query? It's possible?
Yep.
Look at this:

drop table dupa cascade;
create table dupa (a int, b int, c int);
insert into dupa (a, b, c) values (1, 2, 3);
insert into dupa (a, b, c) values (2, 3, 4);

create or replace function ttt(int) returns record  as '
declare
(Continue reading)

Bosko Vukov | 3 Oct 09:36 2004

(unknown)

Hi all!

If I made a C function, let's say
 "ext_data", with an  int  input parameter and  RECORD  or user defined type
(but always only one row) as a output, would I be able to write a SQL query like:

SELECT   st.*, ext.*
FROM     SomeTable st,  ext_data(st.ID)  ext
WHERE    condition only on SomeTable data.

Bosko

Confidentiality Notice: This e-mail, including any documents accompanying this e-mail, contains
information from the offices of TeleTrader Software AG, which may be confidential. This e-mail message
is intended only for the addressee(s). If you are not the intended recipient, be aware that any
disclosure, copying, distribution or use of the contents of this e-mail is prohibited. If this e-mail has
been sent to you in error, please delete this e-mail and any copies or links to this e-mail completely from
your system and notify us immediately so that we can ensure that no further such e-mails are sent to you.
Please also keep in mind that communication via e-mail over the Internet is insecure because of third
parties' possibilities to access and manipulate e-mails. Any views expressed in this message are those
of the individual sender, except where the sender specifically states them to be the views of TeleTrader
Software AG.

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


Gmane