Re: [GENERAL] Suggested minor change to psql
Mark Dalphin wrote: Sometimes, however, rather than using the "\i" command, I would like to simply load my schema directly into psql and capture the output on STDOUT (ie "psql mySchema.sql myOutput"). The problem that arises is that the errors and notices all come out on STDERR. I am not sure this is the right choice. Because of the lack of synchronization between STDOUT and STDERR, it becomes impossible to associate an SQL statement with either a CREATE or an ERROR message. The option, "-e", is supposed to echo the query, but it doesn't help. I have experienced this problem as well. It is a bit of a pain. I would love to hear how others are handling this. I have one partial workaround. % psql -d test -f createdb.sql 21 | less For whatever reason, the above seems to keep the msgs fairly synchronized (at least on Redhat 6.0), making it useful for visual inspection of short loads. Unfortunately, that approach far exceeds my patience for my situation. I'm frequently recreating 150 tables and redoing ~1400 INSERTs via psql with input scripts. That takes about 4 minutes on a dual PII 450 and generates ~15K lines of output (~500 PAGER pages @30 lines/page). Instead, I pipe STDERR/STDOUT to a file, and then grep the file for 'INSERT 0 0', 'ERROR', and other problem signs. I've gotten pretty good at matching up the error msgs with the problem by interspersing judiciously comments and queries, but it's still a pain. It'd be nice to be able to get all psql msgs sync'ed on either STDERR or STDOUT. Cheers. Ed
[GENERAL] get the previous assigned sequence value
I've been looking through the online docs, so far have not found this information. After an INSERT, I want to retrieve the value of the sequence I use for unique_ids Any suggestions ? Kevin Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Production | 333 Texas St #175| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
Re: [GENERAL] How to stop implicit rollback on certain errors?
At 01:18 AM 09-12-1999 +0100, Peter Eisentraut wrote: Seriously, why do you use a transaction, when you don't want any errors caught? Transactions are defined as everything succeeds or nothing goes. If you want update to succeed anyhow, put it in it's own transaction (i.e., commit before it). I want errors caught, most errors abort everything but some errors I want to try a different update instead, if that doesn't work then only rollback everything. I guess that's expected, and I should insert big years using another less ambiguous format. What is the recommended format? The safest way would be to set a date format with SET DATESTYLE TO and use that, possibly assisted by library formatting routines. OK. Link.
[GENERAL] update within limits
Quick question: Is there a way to update fields in a table within limits, similarly to using a select. In other words, something like this: update from table1 field1='blah' where id=(select id from table2 limit 5,5); Thanks, Marc
pg_typemgr ;-) [was Re: [GENERAL] no primary key on self designed type]
Hi Gene,
is this correct
pg_typemgr.html # description of my perl-program
pg_typemgr -p inr -d test inrecord # delete type
pg_typemgr -p inr -f in_record.sql test inrecord # delete and restore
pg_typemgr -p inr -l all test inrecord inrecord.out # list anything
pg_typemgr -p inr -l all test inet inet.out # to compare
When I have to setup pg_amproc like in inet.out ?
Whats about inet | = | hashsel | hashnpage | hash ..
thanks in advance
tom.
"Gene Selkov Jr." wrote:
Hello,
create type inrecord (
internallength=VARIABLE,
input=inr_in,
output=inr_out
);
create table test (
datainrecord not null primary key
);
... result ...
ERROR: Can't find a default operator class for type 268128.
how can I define the default operator class ??
--
The short answer is,
INSERT INTO pg_opclass (opcname, opcdeftype)
SELECT 'inrecord_ops', oid
FROM pg_type
WHERE typname = 'inrecord';
But you won't get away with just that. You probably want a non-empty
opclass. For example, if your type, inrecord, needs a btree opclass,
you'll want to do:
SELECT o.oid AS opoid, o.oprname
INTO TABLE inrecord_ops_tmp
FROM pg_operator o, pg_type t
WHERE o.oprleft = t.oid and o.oprright = t.oid
and t.typname = 'inrecord';
INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
amopselect, amopnpages)
SELECT am.oid, opcl.oid, c.opoid, 1,
'btreesel'::regproc, 'btreenpage'::regproc
FROM pg_am am, pg_opclass opcl, inrecord_ops_tmp c
WHERE amname = 'btree' and opcname = 'inrecord_ops'
and c.oprname = '';
INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
amopselect, amopnpages)
SELECT am.oid, opcl.oid, c.opoid, 2,
'btreesel'::regproc, 'btreenpage'::regproc
FROM pg_am am, pg_opclass opcl, inrecord_ops_tmp c
WHERE amname = 'btree' and opcname = 'inrecord_ops'
and c.oprname = '=';
INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
amopselect, amopnpages)
SELECT am.oid, opcl.oid, c.opoid, 3,
'btreesel'::regproc, 'btreenpage'::regproc
FROM pg_am am, pg_opclass opcl, inrecord_ops_tmp c
WHERE amname = 'btree' and opcname = 'inrecord_ops'
and c.oprname = '=';
INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
amopselect, amopnpages)
SELECT am.oid, opcl.oid, c.opoid, 4,
'btreesel'::regproc, 'btreenpage'::regproc
FROM pg_am am, pg_opclass opcl, inrecord_ops_tmp c
WHERE amname = 'btree' and opcname = 'inrecord_ops'
and c.oprname = '=';
INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
amopselect, amopnpages)
SELECT am.oid, opcl.oid, c.opoid, 5,
'btreesel'::regproc, 'btreenpage'::regproc
FROM pg_am am, pg_opclass opcl, inrecord_ops_tmp c
WHERE amname = 'btree' and opcname = 'inrecord_ops'
and c.oprname = '';
DROP table inrecord_ops_tmp;
Which isn't all yet. The code above assumes that you have defined the
operators, '=', '=', etc.:
CREATE OPERATOR = (
leftarg = inrecord, rightarg = inrecord_code, procedure = inrecord_eq,
restrict = eqsel, join = eqjoinsel
);
If that didn't make you sick already, you also need to define the
procedures, such as inrecord_eq in this example, and possibly write
some c code for them:
CREATE FUNCTION inrecord_eq(inrecord, inrecord) RETURNS bool
AS '${LIBDIR}/inrecord.so' LANGUAGE 'c';
INSERT INTO pg_description (objoid, description)
SELECT oid, 'equals'::text
FROM pg_proc
WHERE proname = 'inrecord_eq'::name;
Thar's, in short, what is required to build a completely new type. One
might as well attempt to borrow some code or the whole opclass from
existing similar types, but I would hesitate to even consider doing that
without the thorough knowledge of the current postgres schema, which
is unfortunately not yet covered by the contemporary docs.
--Gene
--
mit freundlichem Gruss -- regards
,-,
| | Thomas Drillich [EMAIL PROTECTED]
___|__|
(___, ) uniserve Internet Multimedia GmbH
(___, )\ Sophienweg 3
(___, ) \ Technologiezentrum (MIT)
(___,_,)/ \D-59872 Meschede Germany
\ fon: +49 291 59100 , fax: +49 291 59102
Title: pg_typemgr
NAME pg_typemgr [Options] dbname type [|type[,prefix]]
Description
Options
Placeholders
Bugs
Todo
See Also
Copyright
Disclaimer
NAME pg_typemgr [Options] dbname type [|type[,prefix]]
Description
pg_typemgr trys to manage self designed types for postgreSQL.
Options
-h host | --host=host
set the hostname where the postmaster runs, default `localhost'.
-u user | --user=user
connect to database as user.
--pass=password
use password on connect.
-d | --delete
delete type, you have to
Re: [GENERAL] get the previous assigned sequence value
Kevin Heflin wrote: I've been looking through the online docs, so far have not found this information. After an INSERT, I want to retrieve the value of the sequence I use for unique_ids Any suggestions ? Use the OID returned from the INSERT to select the newly created value, i.e., insert into mytable (stuff...) ... select id from mytable where oid = ... How to get the OID in the first example varies depending on your interface (perl/DBI, PL/pgsql, etc). Alternatively, use the currval(mytable_id_seq) function within the same session immediately after the insert. Cheers. Ed
[GENERAL] View / Unions
Is they any guess on when it will be possible to do a view of a union of 2 tables ? thanks Jeff MacDonald [EMAIL PROTECTED] === So long as the Universe had a beginning, we can suppose it had a creator, but if the Universe is completly self contained , having no boundry or edge, it would neither be created nor destroyed It would simply be. ===
Re: pg_typemgr ;-) [was Re: [GENERAL] no primary key on self designedtype]
Hi Gene, is this correct pg_typemgr.html # description of my perl-program pg_typemgr -p inr -d test inrecord # delete type pg_typemgr -p inr -f in_record.sql test inrecord # delete and restore pg_typemgr -p inr -l all test inrecord inrecord.out # list anything pg_typemgr -p inr -l all test inet inet.out # to compare When I have to setup pg_amproc like in inet.out ? Whats about inet | = | hashsel | hashnpage | hash .. I have just fixed cidr indexing in pg_amproc. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] View / Unions
Is they any guess on when it will be possible to do a view of a union of 2 tables ? thanks I think it needs the new multi-Query tree representation, which is not planned until around July. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
