[GENERAL] convert binary string to datum
How does one convert an octet string (e.g. something like a varlena structure) to a Datum? I want to create datums for use w/ heap_form_tuple in a function returning a tuple containing bytea representations of very large integers. TIA -- Ron Peterson https://www.yellowbank.com/ ---(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] convert binary string to datum
2007-10-12_22:22:32-0400 Ron Peterson <[EMAIL PROTECTED]>: > How does one convert an octet string (e.g. something like a varlena > structure) to a Datum? I want to create datums for use w/ > heap_form_tuple in a function returning a tuple containing bytea > representations of very large integers. Is this a legitimate/blessed way to go about it? aval = (bytea *)palloc( len + VARHDRSZ ); VARATT_SIZEP(aval) = len + VARHDRSZ; memcpy( VARDATA(aval), myrawdata, len ); values[0] = PointerGetDatum(aval); ...etc tuple = heap_formtuple( tupdesc, values, &isNull ); -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] convert binary string to datum
2007-10-13_01:22:06-0400 Gregory Stark <[EMAIL PROTECTED]>: > "Ron Peterson" <[EMAIL PROTECTED]> writes: > > > Is this a legitimate/blessed way to go about it? > > > > aval = (bytea *)palloc( len + VARHDRSZ ); > > VARATT_SIZEP(aval) = len + VARHDRSZ; > > memcpy( VARDATA(aval), myrawdata, len ); > > values[0] = PointerGetDatum(aval); > > ...etc > > tuple = heap_formtuple( tupdesc, values, &isNull ); > > Yes, assuming that your tuple descriptor there does in fact have a varlena > data type in column 1. I think that's the part I'm missing. I'm doing: if( get_call_result_type( fcinfo, NULL, &tupdesc ) != TYPEFUNC_COMPOSITE ) ereport( ERROR, ( errcode( ERRCODE_FEATURE_NOT_SUPPORTED ), errmsg( "function returning record called in context " "that cannot accept type record" ))); BlessTupleDesc( tupdesc ); I'm not doing anything explicit to set a varlena datatype in column 1. How would I do that? > And normally you would define your own datatype and not use bytea. Actually, I already have my data in a structure much like varlena. I see PointerGetDatum basically just casts it's argument to (Datum), which is ultimately defined as an unsigned long, I believe. I'm not understanding the magic that PostgreSQL uses to understand whether this is a value or a reference, and whether it's fixed or variable length though. That must be what the tupledesc does, but I don't think I'm setting that up right. I think. Maybe. -Ron- Personally I'm not entirely clear why we don't just use void* for > text and bytea though. > > Postgres 8.3 has a different macro api here though. If you want to > future-proof your code you could do (put the macro definition somewhere in > your private header file after including postgres.h). > > #ifndef SET_VARSIZE > #define SET_VARSIZE(v,l) (VARATT_SIZEP(v) = (l)) > #endif > > aval = (bytea *)palloc( len + VARHDRSZ ); > SET_VARSIZE(aval, len + VARHDRSZ); > memcpy( VARDATA(aval), myrawdata, len ); > values[0] = PointerGetDatum(aval); > ...etc > tuple = heap_formtuple( tupdesc, values, &isNull ); > > Also, make sure you use VARSIZE to refer to the size header at all times, > don't refer to it directly. And unless you mark it with storage plain always > detoast it before working with an argument or anything from heap_deform_tuple. > In postgres we normally put pg_detoast_datum() directly into the DatumGetFoo() > and PG_GETARG_FOO_P() macros. > > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] convert binary string to datum
2007-10-13_08:50:56-0400 Ron Peterson <[EMAIL PROTECTED]>: > 2007-10-13_01:22:06-0400 Gregory Stark <[EMAIL PROTECTED]>: > > And normally you would define your own datatype and not use bytea. > > Actually, I already have my data in a structure much like varlena. Pght, I misunderstood what you were saying. You mean create a full-blown new type. I was thinking completely internally. Yeah, that's probably a better approach - I'll do that. -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] convert binary string to datum
2007-10-13_11:12:05-0400 Gregory Stark <[EMAIL PROTECTED]>: > "Ron Peterson" <[EMAIL PROTECTED]> writes: > > > 2007-10-13_08:50:56-0400 Ron Peterson <[EMAIL PROTECTED]>: > >> 2007-10-13_01:22:06-0400 Gregory Stark <[EMAIL PROTECTED]>: > > > >> > And normally you would define your own datatype and not use bytea. > >> > >> Actually, I already have my data in a structure much like varlena. > > > > Pght, I misunderstood what you were saying. You mean create a > > full-blown new type. I was thinking completely internally. Yeah, > > that's probably a better approach - I'll do that. > > Or you could just define new functions which operate on bytea if you're just > storing binary data. A tuple of two or three bytea values (cryptographic keys); e.g. (bytea, bytea) > I don't understand what you mean with "internally" if you're storing > this in tuples? I thought you were talking about something like a C structure - I think I just misunderstood what you were saying. I'm still a little mixed up about just exactly what, internally, a tuple and a tupledesc are and how they are used. I think I can get where I want to go without completely figuring that out right now though... -- Ron Peterson https://www.yellowbank.com/ ---(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] convert binary string to datum
2007-10-13_13:44:33-0400 Gregory Stark <[EMAIL PROTECTED]>: > "Ron Peterson" <[EMAIL PROTECTED]> writes: > > > I think I can get where I want to go without completely figuring that out > > right now though... > > What are you trying to do? I've implemented the RSA PKCS #1 v2.1 public key cryptography standard in C. Now I'm working on implementing these functions in PostgreSQL. So, for example: select * from generate_rsa_key(); would return a tuple of three very large integers: the modulus, the public exponent, and the private exponent. (Truly secure applications would do this operation on the client, and only export the public key (modulus, public exponent) to the server - but that's another matter) My first thought was to just do something like: CREATE TYPE __full_key AS ( n bytea, e bytea, d bytea ); CREATE OR REPLACE FUNCTION generate_rsa_key( ) RETURNS __full_key AS 'y_pgcrypto.so', 'y_pg_generate_rsa_keys' LANGUAGE C STRICT IMMUTABLE; Instead, I think I'll create two new types: rsa_full_key (modulus, pub exponent, priv exponent) and rsa_part_key (modulus, exponent), that will use hex as input and output for the large integers, like: n: 86161f738222dccb5b7fbb55cf8d7bf70bb71204408807427fb352ad8768f3a61124da267f9a9938b1ca5f16190c428ce0366eb841d11e99bdb93aabbf6caec42c3c0e7469fa6ebaaf12aa8b717049a753685095728ce48a4f557eaae7c00d9ff9f6f962251ebddd60f8886fde8f79f7d2fefe66d73418f7cacea079b87b204bb0cdcd3318c47c1dcd79078fedf984cdf3f8d8feb1cba2ad034f8e1bade70d21683e1bc8baec4afc6d05fa29249a470dcba92792978268360c82fb6432d42bf50f897a1864bff7d4bdf8d86e079e37dfd282f5369f8b4674bcc4bf027cdd0ae7e88aabfee8965c7a23875ae4682a188985afb2a3cd5dcb658666cba31553 e: e12d05c431077bd41ceb79df72bad01c541ca602a8e4091efd74970490626348c21061f5d9b334844109021d6b513b0f3d245dd51fcef89a8c1c9d520d67d92e05def4bdd2d0ca08812f41f3440735ef372355b94ca72ae167cba47a04953785690cba8902f584f12f4195df2d121668b1879a27f8adab7c766d51817e7518a7fd6843e0ee5abad1e60a3968b5b54f13abbfb01c0190e01af51b7ca3d8db0ea221952138d842e376e48a47686af45c0df52caf8ad87d836cb5c26742ee95da3aafd2d0c2cf42d32a0b4f62be3cd5ecf3ff4d72cd9c977f37f09830e5ec3155c3d62a99b075fc80f21ed87744fe35833fc692dfa4bfea06f7fbe13729bbe16e17 d: 5e7501e60b6691280279689ada8a317a5c1a09ef8fab8362ea7d60f24dc36ff3c0e7d7eb0eb6266be7f854c6c59d4d76f34dc349d049a5c8980410b9fe793e94c766e7051db05d54742991649befd0a4587887d60be30a75e57ede3a2aeee9b014997ecceaaf8e00badaa2e3e106b6ab3c5e9fa1aaa8f566077f8bcf906580338b94cc4f357cc4ea1a9c3afe199e7f52c54b0514e081cc9467756aa16a6d75b38655ed80739173d99525ed05c8cef5015b1d7a2e89358ab64e335a107e5efe57d7d9644107f062243d681af084fffcbcfc84e3b13c202de98c96e0c0fdea1990d2a4e993d138dd229ee23f90536f69d1de0bafcc2cc372552eda08d74e7b0287 I know how to create a user-defined type in C, which is probably the better solution anyway; but I was initially thinking I'd just have a keygen function that output a tuple containing the integers as bytea values. I'm getting tripped trying to do that though. -- Ron Peterson https://www.yellowbank.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] convert binary string to datum
2007-10-13_14:15:06-0400 yrp001: > select * from generate_rsa_key(); Am I making this way too complicated? Do I only have to return a C string representation of three bytea values as a tuple? (I still think a bona-fide user-defined type in C is probably better.) -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] convert binary string to datum
2007-10-13_15:22:34-0400 Gregory Stark <[EMAIL PROTECTED]>:
> "Ron Peterson" <[EMAIL PROTECTED]> writes:
>
> > My first thought was to just do something like:
> >
> > CREATE TYPE __full_key AS ( n bytea, e bytea, d bytea );
> >
> > CREATE OR REPLACE FUNCTION
> > generate_rsa_key( )
> > RETURNS
> > __full_key
>
> Oh, incidentally you probably don't want to name your type starting with an _.
> Postgres names array types starting with _ so that's likely to confuse
> something and if not something then at least someone.
Thanks.
I got it working, but returning a composite type of text values, rather
than bytea. I think that's better for me anyway, because I'd like my
type's input and output functions to take hex values instead of the
crazy bytea octet representation. I ended up doing
CREATE TYPE full_key AS ( n TEXT, e TEXT, d TEXT );
-
vals = (char**)palloc( sizeof(char*) * 3 );
// convert key parts to strings
len = mpz_sizeinbase( Y_FULL_KEY_MOD(&akey), 16 ) + 1;
vals[0] = (char *)palloc( len );
gmp_snprintf( vals[0], len, "%Zx", Y_FULL_KEY_MOD(&akey) );
...etc
if( get_call_result_type( fcinfo, NULL, &td ) != TYPEFUNC_COMPOSITE ) {
ereport( ERROR,
( errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg( "function returning record called in context "
"that cannot accept type record" )));
PG_RETURN_NULL();
}
// Make a persistant copy.
td = CreateTupleDescCopy( td );
aim = TupleDescGetAttInMetadata( td );
ht = BuildTupleFromCStrings( aim, vals );
/* make the tuple into a datum */
result = HeapTupleGetDatum( ht );
Someday I'd still like to figure out how to return a composite type
containing bytea values...
--
Ron Peterson
https://www.yellowbank.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
[GENERAL] RSA PKCS #1 v2.1 functions for PostgreSQL
I've put up working code which implements the RSA PKCS #1 v2.1 public key cryptography standard in PostgreSQL. This includes key generation, encryption/decryption, and signing/verification. http://www.rsa.com/rsalabs/node.asp?id=2125 http://www.yellowbank.com/code/PostgreSQL/y_pkcs/ This is alpha quality code. Your comments on anything and everything would be much appreciated. What should the interface look like? Does this work on BSD (my testing to date has been on Linux). How could I improve my code? There's more to do, but this is far enough along that I'd like to make it available. -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] alter column appears to work, but doesn't?
I just updated a table to have a larger column size as follows. alter table attributes_log alter column attribute_name type varchar(48); The size was previously 24. iddb=> \d attributes Table "iddb.attributes" Column | Type | Modifiers +---+- attribute_id | uuid | not null default (encode(pgcrypto.gen_random_bytes(16), 'hex'::text))::uuid attribute_name | character varying(48) | not null management | character varying(24) | not null default 'by_value'::character varying iddb=> insert into attributes ( attribute_name ) values ( 'abcdefghijklmnopqrstuvwxyz' ); ERROR: value too long for type character varying(24) I'm using PostgreSQL 9.0.4 I tried to replicate this with a new database and a simple table, but could not. I had to drop (and then recreate) three rules and a view on this table before altering the column. This is a production database, so I need to treat it gently. -- Ron Peterson Network & Systems Administrator Mount Holyoke College http://www.mtholyoke.edu/~rpeterso -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter column appears to work, but doesn't?
2011-09-05_15:03:00-0400 Tom Lane : > Ron Peterson writes: > > I just updated a table to have a larger column size as follows. > > > alter table attributes_log alter column attribute_name type varchar(48); > > How come this refers to "attributes_log" while your failing command is > an insert into "attributes"? That was a typo, sorry. Did do the same thing on original table. I did the same thing to attributes_log because I have rules that log data there from my original table on insert/update/delete. I just dropped my logging rules, stopped the database and restarted it, put my rules back in place, and now it works. Not sure why. Cached query plan? -- Ron Peterson Network & Systems Administrator Mount Holyoke College http://www.mtholyoke.edu/~rpeterso -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter column appears to work, but doesn't?
2011-09-05_16:14:00-0400 Tom Lane :
> Ron Peterson writes:
> > I just dropped my logging rules, stopped the database and restarted it,
> > put my rules back in place, and now it works. Not sure why. Cached
> > query plan?
>
> Maybe. We'd need a reproducible test case to do more than speculate
> though.
Hi Tom,
I was able to reproduce this. DDL below. Probably more DDL than
necessary, but not sure what is or isn't relevant.
postgres=# drop rule attribute_insert_rule on attributes;
postgres=# drop rule attribute_update_rule on attributes;
postgres=# drop rule attribute_delete_rule on attributes;
postgres=# alter table attributes_log alter column attribute_name type
varchar(50);
...then recreate rules below
postgres=# insert into attributes values ( repeat( 'x', 49 ) );
ERROR: value too long for type character varying(48)
CREATE TABLE attributes (
attribute_name
VARCHAR(48)
UNIQUE
NOT NULL
);
-- Attribute names can be inserted or deleted, but not changed.
CREATE OR REPLACE FUNCTION attribute_name_freeze_tf()
RETURNS TRIGGER
AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN
IF NEW.attribute_name = OLD.attribute_name THEN
RETURN NEW;
END IF;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER attribute_name_freeze_t
BEFORE UPDATE ON attributes
FOR EACH ROW EXECUTE PROCEDURE attribute_name_freeze_tf();
CREATE TABLE attributes_log (
attribute_name
VARCHAR(48),
action
CHAR(6)
NOT NULL
CHECK( action IN ('insert', 'delete','update') ),
changed
TIMESTAMP WITH TIME ZONE
NOT NULL
DEFAULT CURRENT_TIMESTAMP
);
CREATE RULE attribute_insert_rule AS
ON INSERT TO attributes
DO
(
INSERT INTO attributes_log (
attribute_name,
action )
VALUES (
new.attribute_name,
'insert' );
);
CREATE RULE attribute_update_rule AS
ON UPDATE TO attributes
DO
(
INSERT INTO attributes_log (
attribute_name,
action )
VALUES (
new.attribute_name,
'update' );
);
CREATE RULE attribute_delete_rule AS
ON DELETE TO attributes
DO
(
INSERT INTO attributes_log (
attribute_name,
action )
VALUES (
old.attribute_name,
'delete' );
);
--
Ron Peterson
Network & Systems Administrator
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter column appears to work, but doesn't?
Phghght. Sorry, no, that didn't do it, I was typing too fast and skipped updating the attributes table. That was definitely not the case w/ my original database. Wasn't working. The table definition reported the update I made. Insert did not work. Dropping rules, restarting database, and recreating rules got it working. Dunno. -Ron- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] simple tool for building web forms
Can anyone recommend a good tool for building simple web forms w/ a PostgreSQL backend? Emphasis on simple - single table contact info forms, etc. Something that can be presented to end users w/out a lot of hand-holding. E.g. "I want a form w/ last name, first name, # of guests, arrival date. Send notification email of new entries to [EMAIL PROTECTED] and [EMAIL PROTECTED]" Ideally the output could easily be embedded in other web pages. F/OSS preferred, but proprietary not out of the question. This isn't a terribly difficult thing to write, but I imagine it's already been done many times over. I'd rather re-use something existing that re-invent the wheel. Dr. Google hasn't been as helpful as I'd like so far. -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso - I wish my computer would do what I want it to do - not what I tell it to do. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LDAP Authentication
2008-06-29_09:44:01-0400 Taha Ozket <[EMAIL PROTECTED]>: > I have a ldap group, "pgsql-developers". I have an user (user1) member > of this group; > ... > How can I change this line for give login permission to > pgsql-developers members? If you have pam available, you could do pam authentication, and configure pam_ldap to enforce group membership. -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso - I wish my computer would do what I want it to do - not what I tell it to do. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simple tool for building web forms
2008-07-02_12:22:51-0400 jcvlz <[EMAIL PROTECTED]>: > On Wed, Jul 2, 2008 at 8:35 AM, Roberts, Jon <[EMAIL PROTECTED]> wrote: > > http://www.sqlmaestro.com/products/postgresql/ > > > > I've used the PHP Code Generator with great success for simple stuff > > like you describe. You could then write a function to do email > > notifications or whatever you want. > If you're looking to a more complex tool, I would recommend any one of > the popular Content Management Systems (CMSs). I've had success with > many but have been using Drupal lately, combined with the OSS / > flash-based, Flex framework. Thanks guys. I'm familiar with these tools, but they are solving a different problem. I want something *end users* can use to produce their own *simple* web forms. The best solution I've found so far is Zoho Creator: http://creator.zoho.com/ I'm not finding any similar good F/OSS tool in the same space. -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso - I wish my computer would do what I want it to do - not what I tell it to do. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LDAP Authentication
2008-07-03_21:17:50-0400 Ron Peterson <[EMAIL PROTECTED]>: > 2008-06-29_09:44:01-0400 Taha Ozket <[EMAIL PROTECTED]>: > > > I have a ldap group, "pgsql-developers". I have an user (user1) member > > of this group; > > ... > > How can I change this line for give login permission to > > pgsql-developers members? > > If you have pam available, you could do pam authentication, and > configure pam_ldap to enforce group membership. ps - FWIW, I typically make group membership an attribute of the user object itself, rather than maintain groups objects and user objects separately. Primarily because many apps aren't sophisticated enough to deal with having them separated. -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso - I wish my computer would do what I want it to do - not what I tell it to do. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] quoting internal variable names
Hi, I'm trying to use PostgreSQL's internal variables to simplify some shell scripting database setup stuff. Single quotes appear to behave differently in diffent contexts. CREATE USER :v_dbadmin WITH PASSWORD ':v_dbpass'; CREATE USER CREATE USER worked o.k. CREATE DATABASE :v_dbname WITH OWNER :v_dbadmin ENCODING ':v_encoding'; ERROR: :v_encoding is not a valid encoding name here the quotation marks appear to throwing things off :v_encoding is set to SQL_ASCII. If I remove the quotation marks, then I (appropriately enough) get the error: CREATE DATABASE :v_dbname WITH OWNER :v_dbadmin ENCODING :v_encoding; ERROR: syntax error at or near "SQL_ASCII" at character 59 LINE 6: SQL_ASCII; So it seems like the single quotes are causing :v_encoding to be read as a string literal for ENCODING, but they don't do that for WITH PASSWORD. ? -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] quoting internal variable names
On Sat, Feb 19, 2005 at 05:30:25PM -0500, Ron Peterson wrote: > Hi, > > I'm trying to use PostgreSQL's internal variables to simplify some shell > scripting database setup stuff. Single quotes appear to behave > differently in diffent contexts. I decided to just do the proper quoting within the value contained by the variable, and that works fine. If I'm not mistaken, in my example below, the single quote behaviour for ENCODING is in fact correct (:v_encoding should be read as a string literal), and whatever is going on for WITH PASSWORD is incorrect. Is that right? Not a big deal, I have things working, I'd just like to know if I'm mixed up about something here. Best. -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso > CREATE USER > :v_dbadmin > WITH PASSWORD > ':v_dbpass'; > CREATE USER > > CREATE USER worked o.k. > > > CREATE DATABASE > :v_dbname > WITH OWNER > :v_dbadmin > ENCODING > ':v_encoding'; > ERROR: :v_encoding is not a valid encoding name > > here the quotation marks appear to throwing things off > > > :v_encoding is set to SQL_ASCII. If I remove the quotation marks, then > I (appropriately enough) get the error: > > CREATE DATABASE > :v_dbname > WITH OWNER > :v_dbadmin > ENCODING > :v_encoding; > ERROR: syntax error at or near "SQL_ASCII" at character 59 > LINE 6: SQL_ASCII; > > > So it seems like the single quotes are causing :v_encoding to be read as > a string literal for ENCODING, but they don't do that for WITH PASSWORD. > > ? > > -- > Ron Peterson > Network & Systems Manager > Mount Holyoke College > http://www.mtholyoke.edu/~rpeterso > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] uuid c function contrib
Hi, I've written some PostgreSQL C functions which expose the functionality of Theodore Ts'o's UUID library. I need to add a few sanity clauses here and there, but working (mostly) code can be found here: http://www.yellowbank.com/code/PostgreSQL/uuid/ I have one problem. My y_uuid_time function works on my laptop (Kubuntu/Dapper), but not on another box (Debian Sarge). PostgreSQL 8.1.4 in both cases. When it works, it looks like this: rpeterso=# select y_uuid_time( y_uuid_generate_time() ); y_uuid_time - 2006-10-17 14:29:34 When it doesn't, it looks like this: rpeterso=# select y_uuid_time( y_uuid_generate_time() ); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. !> I've been staring at my array bounds so hard it's giving me headache. If anyone can identify what might be wrong, I'd greatly appreciate it. Best. -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] uuid c function contrib
On Tue, Oct 17, 2006 at 03:25:05PM -0400, Ron Peterson wrote: > I've written some PostgreSQL C functions which expose the functionality > of Theodore Ts'o's UUID library. I need to add a few sanity clauses > here and there, but working (mostly) code can be found here: > > http://www.yellowbank.com/code/PostgreSQL/uuid/ > I have one problem. My y_uuid_time function works on my laptop > (Kubuntu/Dapper), but not on another box (Debian Sarge). The usual remedy of walking away from the problem and coming back fresh helped me realize my stupid mistake. I still believe in sanity clauses, so I have a little more finessing to do, but the posted code works. Implements uuid_generation functions which return bytea values, a function to convert the bytea values to the canonical hex form, and a function to extract the time from time-based uuids. If you can use this, please do. Examples: rpeterso=# select y_uuid_to_hex( y_uuid_generate() ); y_uuid_to_hex -- 0bf56bb1-1e47-472f-82a8-cc8134d23d91 (1 row) rpeterso=# select y_uuid_to_hex( y_uuid_generate_random() ); y_uuid_to_hex -- 8e239390-1bd2-4b01-9ad9-8b3f0cfbb5b5 (1 row) rpeterso=# select y_uuid_to_hex( y_uuid_generate_time() ); y_uuid_to_hex -- e3a3a694-5e43-11db-82ad-0014bf7c74ed (1 row) rpeterso=# select y_uuid_time( y_uuid_generate_time() ); y_uuid_time 2006-10-17 20:59:25.101671 Best. -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] uuid c function contrib
On Tue, Oct 17, 2006 at 09:07:08PM -0400, Ron Peterson wrote: > On Tue, Oct 17, 2006 at 03:25:05PM -0400, Ron Peterson wrote: > > > I've written some PostgreSQL C functions which expose the functionality > > of Theodore Ts'o's UUID library. % select y_uuid_time( y_uuid_generate_time() ); y_uuid_time 2006-10-17 20:59:25.101671 My y_uuid_time function returns text. It would be better to have it return a timestamp. What internal functions does PostgreSQL use for timestamp manipulation? int64 or maybe float atimestamp atimestamp = convert_seconds+usecs_since_epoch( somedata ) PG_RETURN_TIMESTAMP( atimestamp ) I'm having a hard time finding any examples of functions returning timestamps I can study to see how they are handled internally. I'm sure it's only a line or two of code. TIA. -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] uuid c function contrib
On Wed, Oct 18, 2006 at 04:31:57PM -0400, Ron Peterson wrote: > I'm having a hard time finding any examples of functions returning > timestamps I can study to see how they are handled internally. I'm sure > it's only a line or two of code. ...I just found date.c -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] c function returning high resolution timestamp
On Wed, Oct 18, 2006 at 04:43:40PM -0400, Ron Peterson wrote:
> On Wed, Oct 18, 2006 at 04:31:57PM -0400, Ron Peterson wrote:
>
> > I'm having a hard time finding any examples of functions returning
> > timestamps I can study to see how they are handled internally. I'm sure
> > it's only a line or two of code.
>
> ...I just found date.c
I'm pretty close, but I'm still not understanding something about
PostgreSQL's internal timestamp representation. If I do 'select
now();', I get a return value with microsecond resolution, which would
seem to indicate that internally, PostgreSQL is using an INT64 value
rather than a float to hold the timestamp. My function below, however,
always takes the float path through the ifdef. If I force the int64
path, I just get a garbage timestamp which still only has a seconds
resolution.
What do I need to do to generate a high resolution timestamp?
TIA.
Ron Peterson
https://www.yellowbank.com/
#include "postgres.h"
#include "fmgr.h"
#include "utils/datetime.h"
#include
#include
#include
#include
PG_FUNCTION_INFO_V1( y_uuid_time );
Datum
y_uuid_time( PG_FUNCTION_ARGS )
{
if( PG_ARGISNULL(0) ) {
PG_RETURN_NULL();
}
bytea* uuid = PG_GETARG_BYTEA_P(0);
typedef unsigned int uint;
uuid_t uu;
struct timeval tv;
time_t t;
Timestamp ts;
uint epoch_offset;
epoch_offset = (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY;
memcpy( uu, VARDATA( uuid ), 16 );
t = uuid_time( uu, &tv );
#ifdef HAVE_INT64_TIMESTAMP
ts = (tv.tv_sec - epoch_offset) * 100 + tv.tv_usec;
#else
ts = (double)(tv.tv_sec - epoch_offset);
#endif
PG_RETURN_TIMESTAMP( ts );
}
---(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] c function returning high resolution timestamp
On Fri, Oct 20, 2006 at 03:32:05PM +0200, Andreas Seltenreich wrote: > Ron Peterson writes: > > > On Wed, Oct 18, 2006 at 04:43:40PM -0400, Ron Peterson wrote: > > I'm pretty close, but I'm still not understanding something about > > PostgreSQL's internal timestamp representation. If I do 'select > > now();', I get a return value with microsecond resolution, which would > > seem to indicate that internally, PostgreSQL is using an INT64 value > > rather than a float to hold the timestamp. > > Floating point timestamps /do/ have microsecond resolution over a > limited range: > > ,[ <http://www.postgresql.org/docs/8.1/static/datatype-datetime.html> ] > | Microsecond precision is achieved for dates within a few years of > | 2000-01-01, but the precision degrades for dates further away. When > | timestamp values are stored as eight-byte integers (a compile-time > | option), microsecond precision is available over the full range of > | values. > ` Ahah! Pghghtht, I've read that page many times, but never looking for programming information. Not a problem with the way the docs are organized, just a problem with the way my brain is organized. Thanks for taking the time to help a slow learner. Working code is posted here: http://www.yellowbank.com/code/PostgreSQL/y_uuid/ -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] PostgreSQL Mhash functions
I created a set of PostgreSQL functions which implement the extended set of digest/hashing functions provided by the Mhash library (http://mhash.sourceforge.net/). For anyone interested, the code is available here: http://www.yellowbank.com/code/PostgreSQL/y_mhash/ Best. -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] when to use pfree?
I just encountered a problem with a C function I've been working on where it broke with the error: could not find block containing chunk ... I tracked the problem down to my use of pfree. Apparently my function was not happy attempting to return a result that was built using values that had been pfree'd. Commenting out the indicated section solved my problem. I understand that one of the advantages of palloc is that allocated memory is automatically returned at some point. My question is, when does it make sense to call pfree? I wouldn't have expected the variables I free'd below to still be needed, but apparently they were. So now I'm feeling a little intimidated about using pfree at all. Should I just save a little wear and tear on my keyboard and forgo the use of pfree altogether? aim = TupleDescGetAttInMetadata( td ); ht = BuildTupleFromCStrings( aim, vals); /* make the tuple into a datum */ result = HeapTupleGetDatum( ht ); ... // pfree( rd ); // pfree( vals[0] ); // pfree( vals[1] ); // pfree( vals[2] ); // pfree( vals ); PG_RETURN_DATUM( result ); TIA -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] when to use pfree?
On Tue, Nov 07, 2006 at 08:36:45AM -0500, Ron Peterson wrote:
> I just encountered a problem with a C function I've been working on
> where it broke with the error:
>
> could not find block containing chunk ...
>
> I tracked the problem down to my use of pfree.
I narrowed the problem down a little bit more. It has nothing to do
with the value I'm returning, it's only the call to 'pfree( rd )' below
that causes me problems.
I did the following, which apparently causes problems. I wrote my own
little function which allocates rd (using palloc).
char*
tp2cp_palloc( char* stringp, const text* textp ) {
int len;
len = VARSIZE(textp) - VARHDRSZ;
stringp = (char*)palloc( len + 1 );
if( ! memcpy( stringp, VARDATA(textp), len ) ) { return NULL; }
if( ! memset( stringp + len, '\0', 1 ) ) { return NULL; }
return stringp;
}
Which I call like
otherfunc() {
char* rd;
if( ! tp2cp_palloc( rd, rand_dev ) )
...
pfree( rd );
}
Apparently pfree hates that.
Should I abandom this idiom altogether? Or is it o.k. to do this if I
avoid the call to pfree (i.e. - will the variable be deallocated
automatically)?
TIA.
--
Ron Peterson
https://www.yellowbank.com/
>aim = TupleDescGetAttInMetadata( td );
>ht = BuildTupleFromCStrings( aim, vals);
>
>/* make the tuple into a datum */
>result = HeapTupleGetDatum( ht );
>
>...
>
> // pfree( rd );
> // pfree( vals[0] );
> // pfree( vals[1] );
> // pfree( vals[2] );
> // pfree( vals );
>
>PG_RETURN_DATUM( result );
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] A couple more PostgreSQL C questions
I have a couple of more PostgreSQL C questions, about the following two
compiler warnings:
warning: ISO C90 forbids mixed declarations and code
This appears to be caused by the following statement:
text* rand_dev = PG_GETARG_TEXT_P(0);
in the following context
PG_FUNCTION_INFO_V1( y_somefunc );
Datum
y_somefunc ( PG_FUNCTION_ARGS )
{
if( PG_ARGISNULL(0) ||
PG_ARGISNULL(1) ||
PG_ARGISNULL(2) )
{
PG_RETURN_NULL();
}
text* rand_dev = PG_GETARG_TEXT_P(0);
...
Should I be concerned by this? What's the proper way to code this?
_
warning: passing argument 3 of 'GetAttributeByNum' from incompatible pointer
type
...caused by the following:
bool isNull;
...
n = GetAttributeByNum( tup, 0, &isNull );
executor.h has:
/*
* prototypes from functions in execQual.c
*/
extern Datum GetAttributeByNum(HeapTupleHeader tuple, AttrNumber attrno,
bool *isNull);
I'm just not seeing what's wrong here...
--
Ron Peterson
https://www.yellowbank.com/
---(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] A couple more PostgreSQL C questions
On Tue, Nov 07, 2006 at 07:50:52PM -0500, Ron Peterson wrote: > I have a couple of more PostgreSQL C questions, about the following two > compiler warnings: > > warning: ISO C90 forbids mixed declarations and code I'm thinking this is unavoidable, and unless my time machine starts working, irrelevant. I'm thinking the correct answer is "just live with it until your version of gcc uses c99 as the default standard". > warning: passing argument 3 of 'GetAttributeByNum' from incompatible pointer > type > > bool isNull; src/include/c.h has typedef char bool; which was conflicting with the definition of bool from elsewhere. I just did my own typdef char pg_bool and used that. -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] composite type insert
I'm trying to understand how to insert a composite type value. I'm having trouble with the last sql statement below. It errors out with: ERROR: cannot cast type record to atype How should I create a composite type value out of columns a and b in table tt that I can insert into table atable? CREATE TYPE atype AS ( acol TEXT, bcol TEXT ); CREATE TABLE atable ( aval atype ); CREATE TEMP TABLE tt AS SELECT 'aaa'::text AS a, 'bbb'::text AS b; INSERT INTO atable SELECT ROW(a, b) FROM tt; Thanks as always. -- Ron Peterson https://www.yellowbank.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] composite type insert
On Sun, Nov 19, 2006 at 02:09:11AM -0500, Tom Lane wrote: > Ron Peterson <[EMAIL PROTECTED]> writes: > > How should I create a composite type value out of columns a and b in > > table tt that I can insert into table atable? > > Hm, it works for me with an explicit cast: > > INSERT INTO > atable > SELECT > ROW(a, b)::atype > FROM > tt; > > Perhaps we should allow this in an automatic or even implicit cast > context. Ah, I can certainly live with a cast. I didn't realize that creating a composite type with 'create type' also automatically created a cast. I should have at least tried that. Maybe section 8.11 (Composite Types) or the 'CREATE TYPE' section of the docs should mention this? Best. -- Ron Peterson https://www.yellowbank.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
[GENERAL] regular expression limit
I believe there's been a change in PostgreSQL's regular expression
handling w/ 8.2.
CREATE TABLE testb (
name
TEXT
--CHECK( name ~ '^[a-f0-9]{1,256}$' )
CHECK( name ~ '^[a-f0-9]{1,255}$' )
);
If I swap the two check statements above, I can no longer insert data.
The operation errors out with:
"invalid regular expression: invalid repetition count(s)"
I'd like the following domain statement to work. It used to work in
8.1.4, but not now. Can I do this in 8.2?
CREATE DOMAIN
__hex_string_8192
AS TEXT
CHECK ( VALUE ~ '^[a-f0-9]{1,8192}$' );
TIA.
--
Ron Peterson
https://www.yellowbank.com/
---(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] regular expression limit
On Mon, Jan 01, 2007 at 11:30:00PM -0500, Tom Lane wrote:
> Ron Peterson <[EMAIL PROTECTED]> writes:
> > I believe there's been a change in PostgreSQL's regular expression
> > handling w/ 8.2.
>
> Compared to what? A repeat count of 256 has been an error at least
> since 7.4, and is documented as such:
>
> : The numbers m and n within a bound are unsigned decimal integers with
> : permissible values from 0 to 255 inclusive.
>
> > I'd like the following domain statement to work. It used to work in
> > 8.1.4, but not now.
>
> Sorry, I don't believe 8.1 took it either.
Hmm, my test table fails in 8.1.4 also, but my actual use case works in
8.1.4 but not 8.2.
CREATE DOMAIN
__hex_string_8192
AS TEXT
CHECK ( VALUE ~ '^[a-f0-9]{1,8192}$' );
CREATE TYPE __rsa_keys AS (
n
__hex_string_8192,
e
__hex_string_8192,
d
__hex_string_8192
);
CREATE OR REPLACE FUNCTION
y_pkcs_generate_rsa_keys(rd TEXT, sl INTEGER, mb INTEGER)
RETURNS
__rsa_keys
AS
'y_pkcs.so', 'y_pkcs_generate_rsa_keys'
LANGUAGE
C
STRICT IMMUTABLE;
When I call y_pkcs_generate_rsa_keys in 8.1.4 it works. When I call
this function in 8.2 it fails with the regular expression limit error.
I'll just revise my constraint as you suggested.
> Consider separating your concerns, eg
>
> CHECK( length(VALUE) <= 8192 AND VALUE ~ '^[a-f0-9]+$' )
That works for me. Thanks.
--
Ron Peterson
https://www.yellowbank.com/
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] octet string type contribution
I have created a fixed length octet string type which anyone so inclined should feel free to use. The purpose of this type is to reduce the overhead associated with storing fixed length binary data in a variable length type like bytea. So far, I've implemented a sixteen octet string type, which would be appropriate for storing MD5 hashes or UUID's (aka GUID's), say. I plan to implement other common sizes. http://www.yellowbank.com/code/PostgreSQL/y_octet_t/ Comments or suggestions for improvement are very welcome. Best. -- Ron Peterson https://www.yellowbank.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
[GENERAL] lanmanager and ntlm hash function contribution
The following location contains PostgreSQL C functions for generating lanmanager and ntlm password hashes. Feel free to use as you see fit. http://www.yellowbank.com/code/PostgreSQL/y_ntlm/ Best. -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] CAST function for user defined type
I've created my own type: y_octet_16. Now I'm trying to create a CAST
function for this type, but I'm not quite getting it.
The input function for my type takes a 32 char hex string as input.
CREATE TABLE bt (
name
TEXT
NOT NULL,
val
y_octet_16
NOT NULL
);
CREATE INDEX
bt_val_ndx
ON
bt( val );
-- this works
INSERT INTO
bt( name, val )
VALUES
( 'aaa', 'abcdef1234567890abcdef1234567890' );
-- this doesn't work, with or without the cast
INSERT INTO
bt( name, val )
VALUES
( 'aaa', encode( y_uuid_generate(), 'hex' )::y_byte_16 );
% INSERT INTO bt( name, val ) VALUES
( 'aaa', encode( y_uuid_generate(), 'hex' )::y_byte_16 );
ERROR: type "y_byte_16" does not exist
LINE 4: ( 'aaa', encode( y_uuid_generate(), 'hex' )::y_byte_16 )
I think my question is: where do I define y_byte_16 as a type that is
recognized by my CAST function? What are the requirements on this
definition? I have:
CREATE CAST
(text AS y_octet_16)
WITH FUNCTION
text_cast_to_y_octet_16( text );
PG_FUNCTION_INFO_V1(text_cast_to_y_octet_16);
Datum
text_cast_to_y_octet_16(PG_FUNCTION_ARGS)
{
text *txtstr;
char *octstr;
if( PG_ARGISNULL(0) ) {
PG_RETURN_NULL();
}
txtstr = PG_GETARG_TEXT_P(0);
octstr = hex2bin_palloc( VARDATA(txtstr), 16 );
PG_RETURN_POINTER( octstr );
}
TIA
--
Ron Peterson
https://www.yellowbank.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] CAST function for user defined type
On Mon, Jan 22, 2007 at 04:36:20PM +0100, Martijn van Oosterhout wrote: > On Mon, Jan 22, 2007 at 09:44:52AM -0500, Ron Peterson wrote: > > I've created my own type: y_octet_16. Now I'm trying to create a CAST > > function for this type, but I'm not quite getting it. > > Quick question: do you mean: > > > val > > y_octet_16 > ^^ > > VALUES > > ( 'aaa', encode( y_uuid_generate(), 'hex' )::y_byte_16 ); > or ^ > > I'm not sure from your example of the difference? byte vs octet. Phghtht, I got tripped up by my own pedantry. I'm using the specific term 'octet' to refer to eight bits, rather than the colloquial 'byte', but instinctively typed 'byte' in my cast. So that's just a typo. That cleared one hurdle, but I'm still not there yet. % select encode( y_uuid_generate(), 'hex' ); encode -- fe43d07c0c624786bebfcb3357a2a13a (1 row) % select 'fe43d07c0c624786bebfcb3357a2a13a'::y_octet_16; y_octet_16 -- fe43d07c0c624786bebfcb3357a2a13a That works, but if I cast the output of 'encode' directly as below, my cast function doesn't work. The error message indicates that it's not seeing a hex string as input. When things are connected up this way, what should the cast function expect to see as input? % select encode( y_uuid_generate(), 'hex' )::y_octet_16; ERROR: input string contains invalid characters Best. -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] CAST function for user defined type
On Mon, Jan 22, 2007 at 11:40:08AM -0500, Tom Lane wrote: > Ron Peterson <[EMAIL PROTECTED]> writes: > > That cleared one hurdle, but I'm still not there yet. > > > % select 'fe43d07c0c624786bebfcb3357a2a13a'::y_octet_16; > > y_octet_16 > > -- > > fe43d07c0c624786bebfcb3357a2a13a > > That's not invoking any cast function, but the type's typinput function > (applied to a C-string). > > > When things are connected up this way, > > what should the cast function expect to see as input? > > > % select encode( y_uuid_generate(), 'hex' )::y_octet_16; > > ERROR: input string contains invalid characters > > If it's a cast from TEXT, it'll get the internal form of a TEXT datum, > ie, a counted (and not null-terminated) string. You might look at > textin() and textout() to grok the difference. Got it working now. Thanks. -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Limit on number of users in postgresql?
On Sun, Jan 28, 2007 at 01:21:09PM -0500, Bill Moran wrote: > The only thing that's missing is row-level granularity. There's at least > one project out there supporting that, and you can also simulate it with > clever usage of stored procedures and the ability to run them with the > permissions of the definer instead of the executer. You can also use rules to protect rows. E.g. CREATE RULE atable__lock_user_insert AS ON INSERT TO atable WHERE CURRENT_USER != 'mysuper' AND new.username != CURRENT_USER DO INSTEAD nothing; CREATE RULE atable__lock_user_update AS ON UPDATE TO atable WHERE CURRENT_USER != 'mysuper' AND old.username != CURRENT_USER DO INSTEAD nothing; CREATE RULE atable__lock_user_delete AS ON DELETE TO atable WHERE CURRENT_USER != 'mysuper' AND old.username != CURRENT_USER DO INSTEAD nothing; -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] programming 'like' operator
Anyone have any tips for an earnest aspiring but somewhat befuddled PostgreSQL acolyte as to what's required to program (in C) a 'like' operator for a user defined type? -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] create operator class problem
I wanted to play with ltree, but I'm having trouble running ltree.sql. Everything in ltree.sql seems to work, except for the CREATE OPERATOR CLASS statements, which error out like: ERROR: syntax error at or near "OPERATOR10" LINE 3: OPERATOR10<@ (_ltree, ltree)RECHECK , ...which is generated from: CREATE OPERATOR CLASS gist__ltree_ops DEFAULT FOR TYPE _ltree USING gist AS OPERATOR10 <@ (_ltree, ltree) RECHECK , OPERATOR11 @> (ltree, _ltree) RECHECK , OPERATOR12 ~ (_ltree, lquery) RECHECK , OPERATOR13 ~ (lquery, _ltree) RECHECK , OPERATOR14 @ (_ltree, ltxtquery) RECHECK , OPERATOR15 @ (ltxtquery, _ltree) RECHECK , OPERATOR16 ? (_ltree, _lquery) RECHECK , OPERATOR17 ? (_lquery, _ltree) RECHECK , FUNCTION1 _ltree_consistent (internal, internal, int2), FUNCTION2 _ltree_union (internal, internal), FUNCTION3 _ltree_compress (internal), FUNCTION4 ltree_decompress (internal), FUNCTION5 _ltree_penalty (internal, internal, internal), FUNCTION6 _ltree_picksplit (internal, internal), FUNCTION7 _ltree_same (internal, internal, internal), STORAGE ltree_gist; I'm running PostgreSQL 8.2.1. I also gave the 'seg' contrib module a whirl, and it also errors out on the CREATE OPERATOR CLASS statement. -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] create operator class problem
On Tue, Feb 06, 2007 at 10:45:23AM -0500, Tom Lane wrote: > Ron Peterson <[EMAIL PROTECTED]> writes: > > I wanted to play with ltree, but I'm having trouble running ltree.sql. > > Everything in ltree.sql seems to work, except for the CREATE OPERATOR > > CLASS statements, which error out like: > > > ERROR: syntax error at or near "OPERATOR10" > > LINE 3: OPERATOR10<@ (_ltree, ltree)RECHECK , > > Looks like something removed the tabs in that line? Ah, yep. For some reason the emacs 'sql-send-region' command in sql-postgres mode is munging that line. If I just read the file directly everything is fine. Thanks. -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] PostgreSQL PAM function contrib
A function to authenticate username/password pairs via PAM. y_pam_auth( username text, password text ) returns bool http://www.yellowbank.com/code/PostgreSQL/y_pam/ This obviously requires that you trust the server. Best. -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] information schema extra fields
I was just reading the PostgreSQL documentation about Information Schemas. Some of the tables have fields which are described as "Applies to a feature not available in PostgreSQL" What does that mean? -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 3: 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] trouble with on insert rule via libpg-perl
On Fri, Dec 10, 2004 at 11:26:09PM -0500, rpeterso wrote: I should have mentioned... PostgreSQL versions 8.0.0beta5 and 7.4.6 libpq-perl 2.0.2 perl 5.8.4 -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] trouble with on insert rule via libpg-perl
I have a simple table, a view, and an on insert rule. These work fine,
ordinarily. But when I attempt to to insert a value into thesis_ps_v
via libpq_sql, nothing happens. If I capture the sql string I'm sending
(as per code sample below), print it to a file, and paste it into a psql
session, it works fine. If I use the same perl code (with the minor
required modifications to the sql string) to insert rows directly into
the the table, that works fine also. I've included the relevant tables
and perl code below.
##
CREATE TABLE thesis (
thesis_sha1
BYTEA
NOT NULL,
ps
BYTEA
NOT NULL,
id
INTEGER
DEFAULT nextval( 'thesis_id_seq' )
PRIMARY KEY
);
CREATE UNIQUE INDEX
thesis__sha1_ndx
ON
thesis( thesis_sha1 );
CREATE VIEW
thesis_ps_v
AS
SELECT
ps
FROM
thesis;
CREATE RULE thesis_ps_v_insert AS
ON INSERT TO thesis_ps_v
DO INSTEAD
INSERT INTO thesis ( thesis_sha1, ps )
VALUES ( digest( new.ps, 'sha1' ), new.ps );
##
sub upload_val ($) {
my( $octstr_ref ) = @_;
my $sqlstr = </secure/tmpdir/test.out" );
print TEST "\n\n$sqlstr\n\n";
close( TEST );
my $result = $CONN->exec( $sqlstr );
my $status = $result->resultStatus;
my $oid = $result->cmdTuples;
if( $CONN->errorMessage &&
! ( $oid eq "" || $status eq PGRES_COMMAND_OK ) ) {
print STDERR sprintf( 'SQL exec failed: %s',
$CONN->errorMessage ), "\n";
}
}
--
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] information schema extra fields
On Sat, Dec 11, 2004 at 11:58:31AM +0900, Michael Glaesemann wrote: > On Dec 9, 2004, at 12:46 PM, Ron Peterson wrote: > > >I was just reading the PostgreSQL documentation about Information > >Schemas. Some of the tables have fields which are described as > > > >"Applies to a feature not available in PostgreSQL" > > The information schema is a part of the SQL standard that allows access > to information about the structure of the database schema. PostgreSQL > strives to implement the SQL standard, but there are some features of > the SQL standard that PostgreSQL has not yet implemented or has decided > not to. You can check SQL conformance in the documentation. Ah, thanks. I didn't realize this was part of the SQL standard. -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] trouble with on insert rule via libpg-perl
On Fri, Dec 10, 2004 at 11:26:09PM -0500, Ron Peterson wrote: > I have a simple table, a view, and an on insert rule. These work fine, > ordinarily. But when I attempt to to insert a value into thesis_ps_v > via libpq_sql, nothing happens. Fixed. I didn't set the schema search path properly in my perl code. -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] bytea internal encoding
How are bytea values encoded internally? Or maybe a better question would be what is the proper way to access bytea data from within a C function? Are there utility functions for reading the bytea data as a stream of scalar values, for example? -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] bytea internal encoding
On Wed, Dec 15, 2004 at 11:08:29PM -0500, Ron Peterson wrote: > How are bytea values encoded internally? > > Or maybe a better question would be what is the proper way to access > bytea data from within a C function? Are there utility functions for > reading the bytea data as a stream of scalar values, for example? If I do something like: bytea* bindat = PG_GETARG_BYTEA_P(0); int32 total_data_bytes = VARSIZE( bindat ) - VARHDRSZ; char* test_cp = (char*)palloc( total_data_bytes ); memcpy( test_cp, VARDATA( bindat ), total_data_bytes ); fwrite( test_cp, 1, total_data_bytes, test_fp ); I get the exactly the same binary file out that I stuffed into PostgreSQL by octal escaping it and doing an insert. Am I asking for trouble writing code like this? -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] bytea internal encoding
On Wed, Dec 15, 2004 at 10:22:07PM -0700, Michael Fuhr wrote: > On Wed, Dec 15, 2004 at 11:08:29PM -0500, Ron Peterson wrote: > > > How are bytea values encoded internally? > > > > Or maybe a better question would be what is the proper way to access > > bytea data from within a C function? Are there utility functions for > > reading the bytea data as a stream of scalar values, for example? > > Do you mean a C function on the server side or on the client side? > > For examples of server-side code, take a look at functions in the > PostgreSQL source that handle BYTEA, e.g., byteacat() in > src/backend/utils/adt/varlena.c. See also the comments concerning > variable-length data and the relevant examples in the "C-Language > Functions" section of the "Extending SQL" chapter in the documentation. Thanks. I was just looking at byteacat. Because byteacat concatenates two bytea arguments, it then seems safe enough to use memcpy in conjunction with VARSIZE to move the data around (they're both the same type, so they'll map one to one). I guess maybe what I'm asking is whether or not it's safe to assume that bytea data will always consist of a string of VARSIZE-VARHDRSZ eight bit bytes, i.e. it's safe to assume 'byte' will always refer to 8 bit values in the range 0..255. -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] transactions in multiple action rules
Do the multiple actions within a multiple action rule implicitly reside within a transaction? I notice that if I brace my multiple actions with BEGIN; and COMMIT;, that I get a syntax error, so I'm assuming that's because they are superfluous in that context... ? (I want to be sure that I can count on 'currval' returning the sequence value returned by the previous action's 'nextval', so that I know I am inserting the proper foreign key into a table after inserting a record into the referenced table.) -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] security
I would like to be able to assert that the security of data stored as a value in a PostgreSQL table can be as high as the security of saving that same piece of data to a file on disk. Would that be correct? I can set table permissions, and even use rules to enforce row level access rights. Of course, the PostgreSQL superuser can circumvent any of these efforts, but that's no different than having root on the OS. There are a number of reasons I'd like to think this, but just to pick a concrete example. Let's say I wanted to implement something analogous to the unix shadow password file. I have a table full of usernames and digested passwords. I set up a rule so that only the username associated with a particular record has access to read or modify the password digest for that record. Unix file permissions restrict access to the the data on disk to the postgres user. This actually seems *more* secure to me than the unix shadow password file, because I can do row level permission checking. Is there some reason I'm not thinking of that it would be crazy to consider using PostgreSQL as a secure data store? Of course I must always fear my own incompetance, but that applies to any system, not just PostgreSQL. -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] security
On Sat, Feb 05, 2005 at 11:00:28PM -0800, David Fetter wrote: > On Sat, Feb 05, 2005 at 09:08:00PM -0500, Ron Peterson wrote: > > I would like to be able to assert that the security of data stored > > as a value in a PostgreSQL table can be as high as the security of > > saving that same piece of data to a file on disk. Would that be > > correct? > > I hate to put it so bluntly, but "security" isn't a product that you > buy or a service that you use. It's not even a rigid set of > procedures, however well-thought-out such a set might be. > > Instead, it's a large and by its nature flexible set of processes that > you must implement and keep up to date. What distinguishes security > in the computer field from other kinds of things involving computers > is the existence of one or more attackers. In re: how to do security, > I'll quote Bruce Schneier's 5-step security evaluation: > >1. What assets are you trying to protect? >2. What are the risks to those assets? >3. How well does the security solution mitigate those risks? >4. What other risks does the security solution cause? >5. What costs and tradeoffs does the security solution impose? > > Until you have answered questions 1 and 2, you can't even start on an > implementation. Sure, I agree with all of that. Those are useful abstractions which basically say that at some point, you need to stop thinking in abstractions, and get down to brass tacks. Using the term "secure" in a loose sense, clearly I hope we can consider it's possible to create secure database applications in PostgreSQL. Otherwise we can tell our clients that PostgreSQL is an inappropriate place to consider storing sensitive information. It's the brass tacks stuff I'd like feel I have a really firm grip on. I've searched, but have not yet found anything resembling a "security best practices" howto for PostgreSQL. I think I have a reasonable grasp of what needs to be done to secure a database application, but I'm worried about that one thing I'm overlooking that leaves me wide open (memory scrubbing?, etc.). As others have said, the additional complications PostgreSQL introduces are the most probable cause of weakness. On the other hand, I don't feel that PostgreSQL is *so* complicated, that those complications can't be dealt with. For example, I agree with Steve Atkins' point that running the database on a separate host eliminates or at least reduces the potential impact of a whole host of issues. Does anyone know of a best practices security checklist for PostgreSQL? Or any other resources in that vein? -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 3: 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] TRIGGER Syntax
What does 'STATEMENT' refer to in the following description for CREATE
TRIGGER?
CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] }
ON table FOR EACH { ROW | STATEMENT }
EXECUTE PROCEDURE func ( arguments )
________
Ron Peterson
[EMAIL PROTECTED]
Re: [GENERAL] Re: PHP and inet data type
mikeo wrote: > > just a curiosity question...is the pg_FieldType() function > something you wrote yourself? It's a PHP function. -Ron-
Re: [GENERAL] Timestamp and compatiblity with MySQL
Daniel Mendyke wrote: > > MySQL allows me to use DATE_FORMAT( col_name, '%D %M %Y' ) > to format the data. What function or method does Postgres > provide for formating timestamp data? Try to_char( timestamp_col_name, 'DD MM ' ). See the section on "Formatting Functions" in the distribution's included html documentation for more details. Ron Peterson [EMAIL PROTECTED]
Re: [GENERAL] Postgresql and programming
If you're only consideration is raw performance of lookups on text fields, I suppose there might be some measureable performance advantage in putting everything in one table, but I doubt you'd really notice the difference. If you *did* do this, what is the significance of the authorID field? Normalize, normalize, normalize. Use a relational database for it's strengths. Don't duplicate data. "T.J.Farrell" wrote: > > In terms of performance also, is it preferable to desing a database as: > > create table articles ( > refarticle text, > title text, > authorID integer, > authorname text, > editorID integer, > editorname text, > ... etc... > ); > > OR : > > create articles( > refarticle text, > title text, > authorID integer, > editorID integer, > ... etc... > ); > > create table authors( >authorname text, > authorID integer, > ...etc... > ); > > create table editors( > editorID integer, > editorname text, > ...etc... > ); Ron Peterson [EMAIL PROTECTED]
Re: [GENERAL] large text fields?
Mike Salib wrote: > > Hi, > I'm trying to find out if I can store more than 8k characters of data in > one field in a single row. I'm working on a web based app and limiting > myself to 8k posts is problematic. Has this limitation changed in > Postgres 7? No. You have three options, I think. (1) Increase the data page size and recompile. (2) Use the large object interface. (3) Check out Jan Wieck's TOAST project (http://www.postgresql.org/projects/devel-toast.html). It's still in development, but maybe you can help. Ron Peterson [EMAIL PROTECTED]
Re: [GENERAL] Help:How do you find that how much storage is taken by the database??
NEERAJ BANSAL wrote: > > Hi, > > How do you find that how much storage the data in the database or tables > is taking in bytes or any other format??? > Is there any command which tells you this??? ls -l /usr/local/pgsql/data/base As a user with access to this directory, of course. This is assuming PostgreSQL has been installed in the usual location. ________ Ron Peterson [EMAIL PROTECTED]
Re: [GENERAL] trigger errors
Marc Britten wrote: > > hi again, > > i created a trigger on a table for insert or delete, and a function that > gets created fine. > > however when i go to insert something into the table i get > > NOTICE: plpgsql: ERROR during compile of create_count_cache near line 2 > ERROR: parse error at or near ";" Hi Marc, Try this function instead: CREATE FUNCTION create_count_cache() RETURNS opaque AS ' BEGIN DELETE FROM LangCount; INSERT INTO LangCount SELECT LangID as ID, COUNT(LangID) AS CNT FROM snippet GROUP BY LangID; DELETE FROM CatCount; INSERT INTO CatCount SELECT LangID as ID, COUNT(LangID) AS CNT FROM snippet GROUP BY LangID; RETURN NEW; END;' LANGUAGE 'plpgsql'; There are two changes from what you have. You cannot use BEGIN/COMMIT (i.e. transactions) within a function or trigger. In plpgsql, use BEGIN and END to delimit your function statements. There is more information about this in the PL/pgSQL portion of the user's guide. The function also needs to return something. A return type of 'opaque' doesn't mean the function returns nothing, it means the function doesn't return a defined SQL datatype. Ron Peterson [EMAIL PROTECTED]
Re: [GENERAL]
Robert Deme wrote:
>
> Hello!!
>
> in flagship ( a variant of clipper for unix , http://www.wgs.com/) it is
> an interesting statement "seek eval" ; it scans the index and for every
> position it evaluates a block of code/ function until the function
> return true .
> is in postgresql an internal mechanism or a way to use the index when
> selecting records with conditions like function(index_expression) =
> value ?
I'm not sure I understand the question. There are a large number of
functions built in to PostgreSQL that you can use in your queries. Plus
you can define your own functions in SQL, C, or other languages. If
you're talking about something else, maybe send an example.
DROP TABLE pie;
CREATE TABLE pie (
filling text,
slice float --size in degrees.
);
INSERT INTO pie
VALUES ('blackbird', 90);
INSERT INTO pie
VALUES ('blackbird', 45);
INSERT INTO pie
VALUES ('plum', 120);
SELECT filling, radians( slice ) AS size
FROM pie
WHERE radians( slice ) > 1;
Ron Peterson
[EMAIL PROTECTED]
Re: [GENERAL] Primary key question
Richard Rowell wrote: > > I'm creating a database with 20 tables or so. I have come to the > table that will likely tie many of these tables together, that is > every single field in this table will be a foriegn key. My question > is, rather then include all of the fields into the primary key of this > table, and therefore include all of these fields into any table that > will reference this one, would it be politically correct to just give > each entry an integer as a primary key and scan for dupes in a > trigger? It sure seems like it would cut down on the complexity of > the whole thing... If I understand correctly, it sounds like what you want to do is establish a UNIQUE constraint on your foreign keys, and add a sequenced integer to be the primary key for this table. E.G. - CREATE TABLE test ( fkey1 INTEGER REFERENCES ..., fkey2 INTEGER REFERENCES ..., id SERIAL PRIMARY KEY, UNIQUE(fkey1,fkey2) ); ____ Ron Peterson [EMAIL PROTECTED]
Re: [HACKERS] Re: [GENERAL] Revised Copyright: is this morepalatable?
Mike Mascari wrote: > > Why do you continue to insist that GPL is superior to BSD? GPL is > BSD *with restrictions*. If someone comes along and sweeps up the > major developers: > > A) Good for the major developers - they deserve to have large > sums of cash thrown their way, particularly for many of them who > have been working on this *for years* > > B) The moment it happens, the project forks and another "Marc" > out-there offers to host development on his machine and the > process begins again. PostgreSQL exists despite Illustra's > existence. > > This is not something new. SunOS, AIX, HPUX, etc. all have (at > one time or another) considerable BSD roots. And yet FreeBSD > still exists... All GPL does is 'poison' the pot by prohibiting > commercial spawns which may leverage the code. If someone makes > some money selling CommercialGres by integrating replication, > distributive, and parallel query, good for them. Is perhaps GPL more restrictive for *developers*? And BSD more restrictive for *consumers*? As a consumer I prefer the GPL. But Mike's point is well taken. I agree that the GPL is rather idealistic. It makes it very difficult, almost impossible, for someone to make money doing software development. Is there a middle ground? Somewhere where perhaps I can be assured that *someday* in the not-so-distant future I, as a consumer, will have access to source code? Is there any such thing as a license with built-in time limits? Reasonably short time limits, as opposed to those provided by the U.S. patent office? Or is there a way to write an open-source license that allows developers to make money? I know, I know, there are too many licenses already. But if talented hard working people can't make a living, there's a problem. This will probably sound very stupid, but would it be possible to write a license that said something to the effect of "if you are a big corporate commercial interest worth more than $X, you must donate $Y to postgresql.org."? I'm not trying to rankle the developers who have benefited me so much by promoting the GPL. I'm just trying to protect myself as a consumer from being left in the cold when the product I've spent so much time learning and implementing suddenly goes proprietary. Sorry to be cynical, but as a consumer, I can't help seeing BSD licenses as good old bait and switch. And this discussion doesn't reassure me otherwise. Sure, the code can fork. SunOS, AIX, HPUX are good examples. Examples of the kind of code forking and corporatism I thought, I hoped, the world was moving away from. Ron Peterson [EMAIL PROTECTED]
Re: [GENERAL] proposed improvements to PostgreSQL license
Chris Bitmead wrote: > > > I would submit that most businesses don't know the difference. Perhaps > > they need some education. > > Are you volunteering? Of course. As the systems administrator for my company, I feel it's my responsibility to inform the principals who run this company about software licensing issues that may affect them. I would hope that anyone with similar duties at any other company would feel the same way. I would like to future proof any technology investments we make to the greatest degree possible. If I say "let's build our project management database using xyz", I would like to assure anyone buying into this proposal that I have some confidence in the future direction xyz will take. The current licensing debate surrounding PostgreSQL has greatly diminished my confidence in it's long term viability as an open source project. I may be very wrong here, but I really don't feel like taking unnecessary chances. The best argument so far as to how a BSD style license will maintain PostgreSQL's viability as an open source project is that if someone take the code proprietary, someone else can fork the code and continue development as an open source project. This has happened in PostgreSQL's own history. How long did it take for the project to get picked up again? How long did it take for the people who picked it up to familiarize themselves with the code? How long did it take before the community at large developed any confidence in the project's viability? How much talent was lost? How many ideas were lost? I believe developers assurances about their desire to maintain PostgreSQL as an open source project are sincere. But I am not going to continue investing my time in PostgreSQL unless those assurances are backed by contractually binding verbage. Ron Peterson [EMAIL PROTECTED]
Re: [GENERAL] proposed improvements to PostgreSQL license
Philip Warner wrote: > > At 10:19 6/07/00 -0400, Ron Peterson wrote: > > > >This has happened in PostgreSQL's own history. How long did it take for > >the project to get picked up again? How long did it take for the people > >who picked it up to familiarize themselves with the code? How long did > >it take before the community at large developed any confidence in the > >project's viability? How much talent was lost? How many ideas were > >lost? > > > >I believe developers assurances about their desire to maintain > >PostgreSQL as an open source project are sincere. But I am not going to > >continue investing my time in PostgreSQL unless those assurances are > >backed by contractually binding verbage. > > Short of employing the core developers directly, and giving them good > enough conditions to ensure that they stay with you, I don't think you can > acheieve your ends. No license change will help. All a licence does is > tells you what *you* can do with the software. > > GPL or BSD, if someone buys up the core developers, their replacements will > have a steep learning curve. Your only choice here is to invest local > talent from your company in the development project so that if the core > developers do leave, then there is a higher chance of a quick uptake. > > Or have I missed something? Did you have some kind of "contractually > binding verbage" in mind? Technically, you're right, of course. There are no absolutes here, just probabilities. Developers will come and go in either case. But in these days of multi-billion dollar mergers and acquisisitons, I would prefer team as a whole be working under a license that would discourage them from taking the code private. Losing any of the developers would be a loss. But losing a significant fraction of them could be tragic. By the way, I hope I'm not coming across as saying "thanks for all your hard work, now go eat peanuts." I have great appreciation for what PostgreSQL has become, and for the developers who have made it so. That is why I keep at this tired argument. I would like to know that this project will continue in a manner that benefits the little people, not just big corporate commercial interests. Perhaps some company _would_ like to pay the developers mucho dinero to continue PostgreSQL development. In fact I really hope so. I would just like to know that if that happened, there would be no obstacle to returning privately developed code to the public domain. Great Bridge has announced their intention to sell both products and services. What are the products? I'm going to bow out of this discussion. (applause!) Sorry if it sounds old and tired to the veterans among us. I'm a little green in the gills and I appreciate your sufferance. Ron Peterson [EMAIL PROTECTED]
Re: [GENERAL] trigger, how to determine action calling?
Marcin Mazurek wrote: > > Hi, > Is it possible to determine easily which action called triger? > For example: > CREATE TRIGGER log_znw BEFORE INSERT OR UPDATE ON tab >FOR EACH ROW EXECUTE PROCEDURE log_tab(); > How to check in called function if it was INSERT or UPDATE? Sure. Here's a snippet of C that does this. You might like to check out the sample SPI programs included with the PostgreSQL source (in /contrib/spi.) if (!CurrentTriggerData) elog(ERROR, "nodeinsert: triggers are not initialized"); if (TRIGGER_FIRED_FOR_STATEMENT(CurrentTriggerData->tg_event)) elog(ERROR, "nodeinsert: can't process STATEMENT events"); if (TRIGGER_FIRED_AFTER(CurrentTriggerData->tg_event)) elog(ERROR, "nodeinsert: must be fired before event"); if (TRIGGER_FIRED_BY_INSERT(CurrentTriggerData->tg_event)) trigger_tuple = CurrentTriggerData->tg_trigtuple; else if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event)) /* trigger_tuple = CurrentTriggerData->tg_newtuple; */ elog(ERROR, "nodeinsert: must be fired on INSERT, not UPDATE"); else elog(ERROR, "nodeinsert: must be fired on INSERT, not DELETE"); -- Ron Peterson Systems Manager Wallace Floyd Design Group 273 Summer Street Boston, MA 02210 617.350.7400 tel 617.350.0051 fax [EMAIL PROTECTED]
Re: [GENERAL] How passwords can be crypted in postgres?
Gordan Bobic wrote: > > > Here's a crypted password: 00xNyXeahk4NU. I crypted it in perl as > > crypt(, salt). So what is ? > > > > The point of a one way hash is that it's, well, one way. Pretty much > > the only way you're going to figure out what password that encrypted > > string corresponds to is to brute force it. Considering that I crypted > > a fairly long random string, that could take you a while. A really long > > while, unless you've got a budget orders of magnitude larger than most > > people. > > [snip] > > > Until the advent of shadow password files, which help defeat brute force > > attacks of the type I just mentioned, the /etc/password file has been > > readable by everyone. It really doesn't matter that much if people know > > the crypted string. They still won't be able to authenticate themselves > > until they know the real password. > > > > So the problem you're trying to defeat by crypting your passwords is the > > problem of someone reading your password file knowing all of your > > passwords. > > > > Now if you're dumb enough to send cleartext passwords unencrypted over a > > public network, you need some schooling. And of course any programs > > doing authentication need to be secure. But that's a different problem > > altogether. > > I was referring to a different aspect of security. I was referring to > preventing more of a "man-in-the-middle" type of attack. If you have a > packet sniffer somewhere between the client and the server, then someone > could read your packet containing the encrypted password and use it to > authenticate to the server, without knowing or caring what the real > password is. If you can send the encrypted password to the server that > matches, you're in. How so? The server is going to take the string you send it, and one-way hash it. If you send it the hash value, it will hash that. Unless that happens to hash to itself, which is exceedingly unlikely, you will not be authenticated. What kind of system are you talking about? -Ron-
Re: [GENERAL] create user, user exists
Tom Lane wrote: > > Ron Peterson <[EMAIL PROTECTED]> writes: > > I'm having a bit of authentication trouble. I'm trying to use 'crypt' > > authentication. PostgreSQL 7.1beta5. My pg_hba.conf is as follows: > > IIRC, you can't use crypt with a flat password file, you have to use > plain passwd authentication. (On a local connection there's not much > point in crypt anyway...) > > BTW, it may help to look in the postmaster log; for many authentication > failures, the error message sent to the client is deliberately not > telling all. The message recorded in the log may have additional > details. I misunderstood the difference between 'crypt' and 'password'. I thought they both did a flat password file, and 'crypt' crypted the passwords, and 'password' didn't. Instead, 'crypt' encrypts passwords sent over the wire, and 'password' authenticates against a flat (crypted) password file, rather than pg_shadow. So local+crypt doesn't make a lot of sense, obviously. So now I'm trying to decide whether I want to use 'password' or pg_shadow for user authentication. Using 'password' seems like a broad (and easily managed) brush, while using groups would give me a finer degree of control over permission settings. I'm using ssl for my remote connections, so the whole 'crypt' thing is irrelevant. -Ron- GPG and other info at: http://www.yellowbank.com/ ---(end of broadcast)--- TIP 3: 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] how to load a sql-file????
Jason Earl wrote: > > psql -U postgres -f loadfile.sql > > Should do what you want. Or if you are already in > psql take a look at the \i command. Especially if you're just starting, you might like to start psql as 'psql -s'. This puts you in single step mode, so when you use \i, the SQL commands you are loading will be executed one at a time. -- Ron Peterson Network & Systems Manager Mount Holyoke College GPG and other info at http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] SSL
The BOFH wrote: > > Just installed 7.1 (first time) and enabled SSL support. Is there a way to > confirm that connections are being made via SSL? The logfile shows a > connection, but doesn't specify whether SSL was used or not. > > I used hostssl in the hba file. You need to compile PostgreSQL with SSL support, and configure pg_hba.conf appropriately. The comments in the provided pg_hba.conf indicate: # ... Note that a "host" # record will also allow SSL connections; write "hostssl" if you want to # accept *only* SSL-secured connections from this host or hosts. If you wanted to be really sure, you could do some packet sniffing... -- Ron Peterson Network & Systems Manager Mount Holyoke College GPG and other info at http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] International Address Format Standard
Is there any such thing as a standard schema for international addresses? Maybe I'm grasping at straws, but one can always hope. I can find information about individual countries easily enough. But how about a general solution? Or is this just pie in the sky? Ron Peterson [EMAIL PROTECTED]
[GENERAL] recursive queries?
Now and again, I find myself wanting to store data in some kind of
variable-level hierarchy. To take a familiar example, let's say the
directory structure on my computer.
So I start to do something like:
CREATE SEQUENCE directory_id_seq;
CREATE TABLE directory {
parent INTEGER,
nameTEXT,
id INTEGER
DEFAULT nextval('directory_id_seq')
PRIMARY KEY
};
INSERT INTO directory
VALUES (1, '\.');
ALTER TABLE directory
ADD FORIEGN KEY (parent)
REFERENCES directory(id)
ON DELETE CASCADE
ON UPDATE CASCADE;
Happy, happy. The problem is, while it's easy enough to define such a
data structure, support for recursive queries is rather lacking. To
unroll a directory tree, you basically have to resort to programming in
.
Not that I really know what I'm talking about when I say 'unroll'. This
data structure is general enough to support cyclic directed graphs. So
what does it mean to 'unroll' such a beasty?
So what's my question then? Well, it seems like maybe there _should_,
or at least _could_ be support for some kinds of common problems. For
example, I would like to constrain my data structure to really be a
tree, and not a potentially cyclical graph.
Does anyone know whether there has been, or ever will be, movement in
this direction among the SQL literati? Am I asking a completely
inappropriate question?
Perhaps these types of problems are what OODB adherents are attempting
to address.
So, to sum, my question: Who plays in this space? Will the SQL
standard itself ever play in this space?
Personally, I'm really only interested in something elegant. Meaning I
don't want to mess around with a solution where this broker communicates
with that broker via an n-way blah blah blah. I can maintain literacy
in several tools at once, but not several dozen. Is my best bet simply
to accept SQL's limitations and program around them in C++ (or C, or
Python, or Perl, etc.)?
Ron Peterson
[EMAIL PROTECTED]
Re: [GENERAL] Win32 Install
Joseph wrote: You're also asking everyone who reads this newsgroup to send you a reciept. Could you turn that auto-ask-for-a-reciept feature off, please? Ron Peterson [EMAIL PROTECTED]
Re: [GENERAL] Starting a new project, any opinions about using 7.0?
"Michael S. Kelly" wrote: > > I'm starting a new project. We've selected PostgreSQL and I'm wondering if > we shouldn't just jump in with 7.0 and avoid conversion hassles later on. Yes. I haven't seen any discussions on this list since 7.0beta1 was released that would indicate you should be concerned about it's stability. Quite the contrary - there have been a number of discussions which concluded by saying something like "...why don't you just try upgrading to 7.0betaX and maybe your problems will go away." At which point the discussion usually ceased. Also, by the time your project, whatever it is, is really underway, I think you can expect to see the final release of 7.0. Don't forget that one of the contributions you can make to the PostgreSQL effort is to provide feedback. Of course feedback on the current product is more useful than feedback on past versions. The features you mention are significant additions to PostgreSQL's capabilities. I'm particularly grateful for the addition of referential integrity constraints. Ron Peterson [EMAIL PROTECTED]
[GENERAL] PostgreSQL User Groups
Does PostgreSQL have local user groups? I happen to live in Boston, so of course anything around here would interest me most. -Ron-
Re: [GENERAL] Re: search.cgi not found on ur site
> On Tue, 16 May 2000, siva wrote: > > and i also wants to know how to find and delete duplicate values from postgres >database(primary key not defined). and i dont wants to go thru. any other books or >reference . give me the correct syntax : The correct syntax for writing a sentence is to capitalize the first letter of the first word. End the sentence with a period immediately following the last word. There should be no space between the word preceding a colon and the colon, either. The first person singular 'I' should also be capitalized. The proper spelling is "PostgreSQL", not "postgres"; and "through", not "thru". For example: "I don't want to go through any other books or reference material." rather than: ". and i dont wants to go thru. any other books or reference ." I'd suggest you reconsider your stance against fancy book-learning. You'll have to spend an awfully lot of time on the discussion forums to find out everything you need to know about running PostgreSQL, if you don't do any of your own homework. You might begin by examining the use of SELECT DISTINCT. -Ron-
Re: [GENERAL] loading data from flat text file
> "Voitenko, Denis" wrote:
>
> So I am almost there, except my data is formated as follows:
>
> "chunk1","chunk2","chunk3"
>
> how would I tell COPY that my data is encapsulated in " and separated
> by , ? Furthermore, I did not find a manual on the COPY command.
> Anyone?
In /usr/local/pgsql/doc (assuming that's where you installed PostgreSQL)
you will find a good deal of html format documentation. From the docs:
COPY [ BINARY ] table [ WITH OIDS ]
FROM { 'filename' | stdin }
[ [USING] DELIMITERS 'delimiter' ]
[ WITH NULL AS 'null string' ]
COPY [ BINARY ] table [ WITH OIDS ]
TO { 'filename' | stdout }
[ [USING] DELIMITERS 'delimiter' ]
[ WITH NULL AS 'null string' ]
Basically, you just need to specify the delimiters.
Bruce Momjian is also in the process of writing a book on PostgreSQL.
You can download a snapshot of its current state from
http://www.postgresql.org/docs/awbook.html.
-Ron-
[GENERAL] SQL Recursion
Aha! I have had this thing about trying to figure out how to program trees/hierarchies/graphs in SQL for awhile now. Something I've complained about in this list an others, including a Byte newsgroup. Well whaddya know, but I read Jon Udell's Byte column today, and it begins by quoting my complaint: http://www.byte.com/column/BYT2518S0001. There's a great link further down in the article (http://www.dbmsmag.com/9603d06.html) that addresses this very subject. Check it out. There *are* solutions to this problem!!! You can bet I'll be buying Joe Celko's book shortly. Anyway, just wanted to share my glee. -Ron-
Re: [GENERAL] Migrating from mysql.
Joe Karthauser wrote:
>
> Hi there,
>
> I'm migrating from MySQL to PostgreSQL and I was wondering whether someone
> could help me match some datatypes.
I'm trying to learn PostgreSQL myself. They say there's no better way
to learn than to teach, so here goes...
> Firstly MySQL has a 'timestamp' datatype which automatically updates with
> the current timestamp whenever an instance containing it is inserted or
> updated. Is there an equivalent datatype in PostgreSQL?
No. Try a combination of default value and an update rule. I've
included an example below. There was a discussion on this list recently
about when to use rules vs. triggers. You might want to read the
archives about that. Something I need to review more myself.
> Secondly MySQL supports an 'enum' datatype which allowed a number of
> labels to be defined as valid datatypes for a column, i.e:
>
> I can't seem to find the equivalent in PostgreSQL. Is there a way of doing
> this?
>
Yes. Use a CHECK constraint. I included one in the following example.
CREATE SEQUENCE mucho_mas_id_seq;
CREATE TABLE mucho_mas (
nameTEXT CHECK( name IN ('Larry','Billy')),
worth NUMERIC(14,2)
NOT NULL
DEFAULT '0',
updated TIMESTAMP
DEFAULT CURRENT_TIMESTAMP,
id INTEGER
DEFAULT NEXTVAL('mucho_mas_id_seq')
PRIMARY KEY
);
INSERT INTO mucho_mas (name, worth)
VALUES ('Larry','400.00');
INSERT INTO mucho_mas (name, worth)
VALUES ('Billy','400.01');
-- no mucho mas for me.
--
INSERT INTO mucho_mas (name, worth)
VALUES ('Ron','2.03');
SELECT *
FROM mucho_mas;
-- we need to updates on a view, rather than on table itself, because
-- if we update the table directly, we will have a circular rule
-- combination
--
CREATE VIEW mucho_mas_view AS
SELECT * FROM mucho_mas;
CREATE RULE mucho_mas_view_update AS
ON UPDATE TO mucho_mas_view
DO INSTEAD
UPDATE mucho_mas
SET worth = new.worth, updated = CURRENT_TIMESTAMP
WHERE id = old.id;
-- in real life, you'd probably do something more sophisticated to
-- select proper id value, but that's another problem.
--
UPDATE mucho_mas_view
SET worth = '400.02'
WHERE id = 1;
-- Hmm, must have been an accounting mistake. Let's fix that.
--
UPDATE mucho_mas_view
SET worth = '400.03'
WHERE id = 2;
SELECT *
FROM mucho_mas;
DROP VIEW mucho_mas_view;
DROP TABLE mucho_mas;
DROP SEQUENCE mucho_mas_id_seq;
> And last but not least I'm used to using the 'desc tablename' sql command
> to show the structure of a table within MySQL. How do I do the same in
> PostgreSQL.
I noticed someone already responded to this, so I won't repeat.
Good luck! I hope I haven't led you too far astray!
-Ron-
