Re: [SQL] PG/DBI: 'NOTICE: UserAbortTransactionBlock and not in in-progress state'
Markus Wagner wrote: > > Hi, > > I am using PG 6.5.1 and DBI. My Perl programs always print to stderr: > > "NOTICE: UserAbortTransactionBlock and not in in-progress state" or > "NOTICE: EndTransactionBlock and not inprogress/abort state" > > I tried to do -> finish and -> commit after each > query, but the messages still appear. > > Any hints? I think that happens if the backend aborted (elogged) with an ERROR message and you did not start a new transaction. ERROR kills the current transaction altogether in 6.5.*, IIRC. Regards, Ed Loehr
Re: [SQL] POSTGRESQL and PERL?
Peter Landis wrote:
>
> but what if you have a variable set like:
> $sort_selection = "name";
>
> How do you sort by the variable?
>
> For instance you cannot do...
> $sqh = $dbh->prepare(q{select name from company order
> by $sort_selection;});
Why not? The query string is created before prepare is called...
Regards,
Ed Loehr
Re: [SQL] psql problem
What is the definition of the table 'some_table'?? Regards, Ed Loehr Rick Parker wrote: > > Does anyone know why when I am in a particular DB as user postgres and use > the following statement, why I get this error?" > > This is the statement; > SELECT * FROM some_file where ID = 1; > > > Error: ERROR: attribute 'id' not found > Execution time 0.02 sec. > > But if I use the following statement, everything is returned fine. > > SELECT * FROM servlet_file; > > Thanks ahead of time, > > Rick Parker
Re: [SQL] how to know when a table is altered
Vincenzo Passoli wrote: > > i'm developing a framework (mod_perl+apache) that reads the db-schema and > explode html forms. > > now i read the schema and cache it into perl-hashes to speedup things. > > my problem is to recognise when a table is altered so that the framework can > update the related forms connected to the db tables. > i don't want to read the schema every time. > > How can i implement this ? My sub-optimal approach was to cache all of the generally static tables (requiring a restart to reload them if they changed), and query the rest. You can avoid a lot of joins by querying the db for the foreign keys to static tables and then looking them up only in the app cache. But caching query results and invalidating them when the underlying tables changed would greatly simplify my app and speed things up, so I'd love to hear if others have a better/faster solution here. Regards, Ed Loehr
Re: [SQL] oracle rownum equivalent?
mikeo wrote: > > is there an equivalent to rownum in oracle? > > also, where can one find reference to "hidden columns" > such as OID or, as in oracle, rownum? oid is the equivalent. not sure documentation exists for these... Regards, Ed Loehr
Re: [SQL] how to know when a table is altered
Ed Loehr wrote: > > Vincenzo Passoli wrote: > > > > i'm developing a framework (mod_perl+apache) that reads the db-schema and > > explode html forms. > > > > now i read the schema and cache it into perl-hashes to speedup things. > > > > my problem is to recognise when a table is altered so that the framework can > > update the related forms connected to the db tables. > > i don't want to read the schema every time. > > > > How can i implement this ? > > My sub-optimal approach was to cache all of the generally static tables > (requiring a restart to reload them if they changed), and query the > rest. You can avoid a lot of joins by querying the db for the foreign > keys to static tables and then looking them up only in the app cache. > But caching query results and invalidating them when the underlying > tables changed would greatly simplify my app and speed things up, so I'd > love to hear if others have a better/faster solution here. I was thinking about another possible approach (and definitely half-baked). I'd call it "table-based caching". Suppose you created a table specifically for tracking how recently a table had been updated, e.g., create table table_status ( tablename varchar not null unique, last_change timestamp not null ); Then create triggers for every table that updated table_status.last_change = now() on every UPDATE/DELETE/INSERT. Then, to determine when you need to invalidate the application cache, you'd load this table at the beginning of the request and invalidate cache entries involving tables with table_status.last_change more recent than when the query results were cached. If, like most DBs yours is mostly reads, you'd suffer one pretty light DB query in order to validate your cache on each request. Then, each That would be a significant hit on big changes involving many records. But where that's unusual, it might be a big win. There are a lot of gotchas with this approach (figuring out the query-table dependencies, etc.), but it seems possible. BTW, I thinking server-side caching is the optimal solution here. I previously lobbied -hackers for implementing a server-side result-set cache in which entire query result sets could be cached (up to a configurable limit) and returned immediately when none of the underlying tables had changed (http://www.deja.com/viewthread.xp?AN=602084610&search=thread). I still think that would be a huge performance win in the vast majority of systems (including mine), but it is not supposedly not trivial. The idea won absolutely no fans among the developers/hackers. There was some talk about caching the query plans, but I think that ultimately got dismissed as well. I wish I had time to work on this one. Regards, Ed Loehr
Re: [SQL] oracle rownum equivalent?
mikeo wrote: > > thanks for the response. oid is equivalent to oracle rowid. > rownum can be used similar to the limit option of select in postgres > but in oracle it can also be used in a where clause, or as an assigment > in an update statement for instance. > > eg: update ctmaster set bsc_id = mod(rownum,3) +1; > > this gives me a way to assign streams to rows in a load balanced manner > on the fly, for example. i use it in other more involved ways than this > also. i cannot do this with limit. i could do this with sequence with > a max value but i'd have to define a sequence each time i wanted to do > something "on the fly" or for what ifs. > > what i'm also interested in is how to find reference to these type of > pseudo-columns, even just the names of them, if they're listed somewhere. I think this might be the list, but you might query pgsql-hackers for more info. There was a recent thread involving this.. ctid oid xmin (minimum transaction number) xmax cmin (minimum command number) cmax ctid may be what you're looking for, but I don't understand very well how these are used. Maybe someone else can say or you can experiment... Regards, Ed Loehr (PS: Posting to only one of -general or -sql will almost always be sufficient.)
Re: [SQL] how to know when a table is altered
Vincenzo Passoli wrote:
>
> 3.SQL does't have a TRIGGER on this 'event' (CREATE TRIGGER mytrig ON
> mytable FOR ALTER AS ...). Can be Added ?
I don't know. Maybe someone else does (though I think pgsql-sql is very
low volume...pgsql-general would get a lot more readers).
> 4.May be beautiful if the db tells to the app when a trigger is fired, so
> the app can update thing without go crazy with asking that to the db every
> time. Is there a solution?
Maybe. Check out NOTIFY (and LISTEN) at
http://www.postgresql.org/docs/postgres/sql-listen.htm
http://www.postgresql.org/docs/postgres/sql-notify.htm
I haven't tried it, not sure it fits into DBI's API or model. I'd like
to hear if you use it with success (or anyone else who is already using
it successfully within modperl/DBI).
If it turns out that DBI can't handle LISTEN/NOTIFY, it might be possible
to do it through a 3rd app that somehow listens and signals the modperl
servers (yuck).
> 5.For the query table dependencies (a proposal, i've not used this
> solution!):
>
> $sql= "select a.f1,a.f2,b.f3,c.f4 from t1 as a, t2 as b, t3 as c where
> "
> we can extract the tables used in a query
>
> instead of writing $sql=as before, write a thing similar to (supposing
> DBI+perl+mod_perl)
>
> my @array;
> my $ptr_array= \@array;
>
> $sql = "select a.f1,a.f2,b.f3,c.f4 from
> ".&add_check_table('t1',$ptr_array)." as a,".
>&add_check_table('t2',$ptr_array) . " as b,
>
> ---> &add_check_table=sub to push table to check in the array @array, return
> the name of the table, i.e. t1, t2 ...
>
> then
>
> call &do_check ($ptr_array)
> using table_status, the sub do_check return 1 if min(last_changes for every
> table in @array) is older that the caching of this query results, we must
> have the query result somewhere (on ( properly locked) files?) and the last
> time we perfomed the query.
>
> then
>
> if (&do_check($ptr_array)){
>fetch rows
>store in cache
> }
> -->use the cache
I haven't seen that syntax before with your use of "as", but I get your
gist. Sounds reasonable, though it looks like a major pain, stealing
most of the pleasure and convenience of SQL. I'd almost be tempted to
build a regex'er to pick out the table names from each query in a layer
between DBI and the app until the regex performance became an issue.
Regards,
Ed Loehr
Re: [SQL] Newbie question on how to check triggers created
Chunky wrote: > > Could someone please enlightenment me what command in psql i should use > to show the various triggers and rules that i have created? These might work... select tgname from pg_trigger select rulename from pg_rewrite Regards, Ed Loehr
Re: [SQL] Outer join in postgresql
Patrick Kay wrote: > > I am looking for a way run an outer join in psql. Can anyone help? > > Informix has an "OUTER" keyword. I don't see anything like this in the docs > for psql. There are many examples on how to do this in the archives or via deja.com's power search. Regards, Ed Loehr
Re: [SQL] Type conversion
Tom Lane wrote: > > Ice Planet <[EMAIL PROTECTED]> writes: > > B: insert into t2 values (select int2(int4(b)) from t1 where a = 1) > > Works for me when spelled correctly: I think you can also leave out the 'values' for a sub-select insert, though I haven't checked to see if it matters... Regards, Ed Loehr
Re: [SQL] Wildcard in date field???
Web Manager wrote:
>
> I need to make a query that will select items that have a date matching
> the current month for example.
>
> For June this means that any day between 2000-06-01 and 2000-06-30 are
> ok. To do that I need a wildcard like "%" to replace the actual day in
> the date field.
>
> Ex.: select * from item where date = '2000-06-%%';
Multiple ways to do it, but here's one:
select *
from item
where date_part('month',mydate) = 6
and date_part('year',mydate) = 2000;
Regards,
Ed Loehr
Re: [SQL] timespan casting
Jeff MacDonald wrote: > bignose=> select start,stop, stop-start as start_stop from foo; > start | stop | start_stop > ++ > 2000-06-22 20:37:12-03 | 2000-06-22 20:37:12-03 | 00:000 > > now my question.. first of all is the first start_stop result a little > off ? (talking about the 3 0's..) What's off about it?? Your start and stop appear identical. > second is there a way i can do a select so it says something more human > usable ie : 100 days 22 hours 32 minutes.. Try to_char(). http://www.postgresql.org/docs/postgres/x2976.htm Regards, Ed Loehr
Re: [SQL] A subselect in an aggregate
Bryan White wrote:
>
> This statement works:
> select date, (select sum(qty * price) from orderdetail d where d.orderid =
> orders.orderid) from orders
>
> But when I try to do something like this:
>
> select date, sum(select sum(qty * price) from orderdetail d where d.orderid
> = orders.orderid) from orders group by date
>
> I get ERROR: parser: parse error at or near "select"
>
> Is there a way to apply an agregate function to a subselect like this?
Avoiding the question, I'm wondering if this simpler form wouldn't be
what you're after?
select o.date, sum(d.qty * d.price)
from orderdetail d, orders o
where d.orderid = o.orderid
group by o.date
Regards,
Ed Loehr
Re: [SQL] pg_dump problem
Graham Vickrage wrote: > > I am trying to backup a production database running on v6.5 and restore it > on our test machine running v6.5. > > The largest table has about 750,000 rows, the other 5 tables are very small > approx 100 rows. > > When I try to restore the database using "psql -e database < db.out" I get > the error message > "query buffer max length of 16384 exceeded" after each row. > > Would somebody please tell me how to increse this buffer (assuming this is > whats required to solve the problem) as I have looked though the > documentation and I am still struggling :-( You probably don't need to increase the buffer. More likely, there is an unterminated string constant somewhere in db.out. Yes, that would probably be a bug in pg_dump, but I do vaguely recall problems in 6.5* with pg_dump. You might search deja for your error message. Regards, Ed Loehr
Re: [SQL] SERIAL type does not generate new ID ?
Jean-Marc Libs wrote:
>
> I have this table:
>
> CREATE TABLE film (
>film_id serial,
> ...
>
> SELECT setval ('film_film_id_seq', 6);
>
> I have this query in PHP:
> insert into film
>
>(film_country_id,film_country_id2,film_country_id3,film_country_id4,film_prod_year,film_ec_certif,film_ce_certif,film_eur_support,film_media_support,film_provisoire)
> values ('FR','','','','2000','f','f','f','f','f')
>
> And it gives the following error:
> ERROR: Cannot insert a duplicate key into a unique index
You should not have to do anything special with the serial or the
sequence (including setting it to 6). Possible sources for for your
error: 1) you are resetting the sequence value to 6 when you already
have a row with that value for film_id in the table, or 2) could be the
message is coming from a triggered insert "downstream" from your initial
insert (see your server log). BTW, 7.0+ tells you *which* index caused
the problem.
Regards,
Ed Loher
Re: [SQL] Timestamp problem
Bernie Huang wrote: > > table > - > ... > borrow timestamp > return timestamp > > Now, I thought timestamp is in sec eg; 35434434, but in PostgreSQL, it > shows up as datetime datatype eg; 2000-06-07 17:00:05-07. > > I was wondering is this format a correct one for timestamp, or is it a > bug? Abd if I want to show the time, each retrieval I have to chop of > the '-07' timezone at the end. Is there a way to avoid this being show > up in Postgres? You might want to check out the date/time and formatting functions at http://www.postgresql.org/docs/postgres/index.html Regards, Ed Loehr
Re: [SQL] Re: Matching and Scoring with multiple fields
Tim Johnson wrote: > > I have a table like this: > > a,b,c,d,e,f,g,h > --- > 2,5,3,4,4,5,2,2 > 1,1,1,1,1,1,1,1 > > a to h are of type int. > > I want to take input values which relate to this table say: > how do you feel about a: > how do you feel about b: > how do you feel about c: > ... > > and the answers will be 1 to 5. > > Now I want to take those answers for my incoming a to h and scan down the > table pulling out the closest matches from best to worst. I wonder if you don't really just want to find the vector(s) closest in N-space to the input vector. You might dig up an old 3-variable calculus book, find the formula, and write a PL/pgSQL function to compute the distance between two N-dimensional vectors... Regards, Ed Loehr
