[HACKERS] unsubscribe
-- Best,Gourish Singbal
Re: [HACKERS] Scan Keys
On Wed, Jul 05, 2006 at 09:14:21PM -0400, Greg Stark wrote: Well what was tripping me up was figuring out the operator class. I just realized it's in the index's Relation object. But yes what you describe is really a problem. Even given the operator class there's no way for me to know which strategy number to pick. There might not be any strategy number for equals in which case I'm in trouble. Well yes, that's an issue. Currently it's assumed by various parts of the backend that the EqualStrategy operator of a btree operator class is what you can use to decide what's equal. Although it's not really necessary, there is currently nothing else in the system that really tells you the answer to the question you ask... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[HACKERS] unsubscribe
Re: [HACKERS] Scan Keys
Greg Stark [EMAIL PROTECTED] writes: I'm a bit confused about how scan keys work. Is there any simple way given a list of Datums of the same type as the index tuple attributes to get all matching index entries? This is for a non-system index. Define matching. I tried just using index_getprocinfo(...,BTORDER) with InvalidStrategy like btree does but _bt_preprocess_keys runs into problems without a valid strategy number. And in any case that would be btree specific which seems like it ought not be necessary. There's no particularly good reason to suppose that a non-btree index necessarily supports equality probes at all. For instance if you're using GIST or GIN for full-text searching, the index might only know about component words, not the whole strings that are in the table. Opclasses designed for spatial databases might conceivably not have equality either (though that's a bit more of a stretch). As Martijn pointed out, we rely on btree-opclass equality as the main means of deciding what equality really is. I don't think it'd be wise to insist that every index opclass, no matter what its purpose, has to include an equality operator. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] lastval exposes information that currval does not
On Wed, Jul 05, 2006 at 05:57:08PM -0700, Joshua D. Drake wrote:
I am well aware of what security definer means. The significant part of
this example is that lastval() will allow the caller to see the value of
a sequence where currval('seq') will not. This means that things which
might have been forbidden in 8.0 are now accessible in 8.1.
It also means that revoking usage on a schema is not sufficient to
prevent a user from accessing things within that schema, a property that
makes me quite uncomfortable.
Then the public schema must drive you nuts :). If you were to create the
function as a non-super user you would probably be good.
Joshua D. Drake
I use the public schema for public things. You are still missing the
point of the example. I could have written it any number of other ways.
I could have granted update, but not select on the sequence to the
non-superuser.
Not using security definer will not change the fact that although I can
not use currval to get the current value of the sequence, I can get it
with lastval. This behavour is unexpected, not explicitly documented,
not really useful, and new in 8.1. This means it could potentially open
new security holes in existing programs. The suprising and hardly
documented behaviour means that new programs are likely to be
vulnerable.
More importantly, it reveals that the security check for schema usage is
not part of the ACL check for actions like selecting a table. This means
that revoking usage on a schema provides no security guarantee. For
example:
test=# create schema private;
test=# grant usage on schema private to public;
test=# create table private.insecure as select 1;
test=# grant select on private.insecure to public;
test=# set role pfrost;
test= prepare exploit as select * from private.insecure;
test= reset role;
test=# revoke usage on schema private from public;
test=# set role pfrost;
test= select * from private.insecure;
ERROR: permission denied for schema private
test= execute exploit;
?column?
--
1
(1 row)
test=
I hope the above example is strong enough to elicit a comment from a
qualified developer. If it is not, consider that stored procedures
contain prepared statements, and many client applications cache prepared
statements as well. Thus, revoking usage on a schema is about as good as
nothing until all sessions have ended. It also means that any function
which operates with OIDs can potentially bypass the schema usage check.
---(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: [HACKERS] [GENERAL] UUID's as primary keys
On Thu, Jun 29, 2006 at 06:47:17PM +0200, Martijn van Oosterhout wrote: It seems to me that maybe the backend should include a 16-byte fixed length object (after all, we've got 1, 2, 4 and 8 bytes already) and then people can use that to build whatever they like, using domains, for example... So... Back to this. It won't happen unless somebody does it - and I realize that people are busy with their own projects, so unless somebody more willing and better suited will step up, I'm going to take a stab at getting advanced consensus. Please answer the below questions, and state whether your opinion is just an opinion, or whether you are stating it as a PostgreSQL maintainer and it is law. If you wish, you can rank preferences. 1) The added 128-bit type should take the form of: a) UUID, with all functions b) UUID, with only basic generation functions + encode/decode/indexable c) UUID, with only encode/decode/indexable - generic except for the name of the type, and the encoding format. d) Generic 128-bit type - same as c) except may not encode or decode as UUID (dashes). Either a large number (hex string?), or binary data. e) Generic n-byte binary data type generator. Not sure of feasibility of this at this point. See thread. 2) According to your answer in 1), the added 128-bit type should be: a) In core first. b) In contrib first. c) In pgfoundry first. Thanks, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(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: [HACKERS] [GENERAL] UUID's as primary keys
On Thu, Jul 06, 2006 at 12:12:18PM -0400, [EMAIL PROTECTED] wrote: Please answer the below questions, and state whether your opinion is just an opinion, or whether you are stating it as a PostgreSQL maintainer and it is law. If you wish, you can rank preferences. Do I have to pick only one? I'd choose firstly for: 1c) UUID, with only encode/decode/indexable - generic except for the name of the type, and the encoding format. 2a) In core first And in addation to that: 1b) UUID, with only basic generation functions + encode/decode/indexable 2b) In contrib first. And maybe finally: 1a) UUID, with all functions 2c) In pgfoundry first. IOW, I'm not so convinced that full UUID support should appear in core, but I think a 16-byte type should be available in core, with basic UUID functions in contrib and the full suite on pgfoundry. But that's just my opinion ofcourse. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] [GENERAL] UUID's as primary keys
On 7/6/06, [EMAIL PROTECTED] wrote: Please answer the below questions, and state whether your opinion is just an opinion, or whether you are stating it as a PostgreSQL maintainer and it is law. If you wish, you can rank preferences. 1) The added 128-bit type should take the form of: a) UUID, with all functions 2) According to your answer in 1), the added 128-bit type should be: a) In core first. Opinion, 1 a, 2 a Jochem ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Help with casting and comparing.
I looked around in the code and the whole thing looks complex and prone to breaking my code often, i.e., whenever someone will decide to change the casting/operators. I thought about just issuing in SPI_prepare query the proper casting like: SELECT a0::text,a1::text ... Casting to equal types (when neccessary) will allow me to just use regular equality functions. And perhaps the added benefit is that the casted values are cached? since i restart cursor scans often(by moving to start not reopening). The downside is that i noticed that the CTID is removed from the tuple if a cast occurs. Is there a way to tell postgresql to not remove the CTID? The other way, of course is to add CTID as an attribute in the query but it seems less efficient since i am accessing it repeatedly. On Wednesday 28 June 2006 18:12, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: On Wed, Jun 28, 2006 at 03:25:57PM +0300, Tzahi Fadida wrote: I need help finding out how to determine if two types are equality compatible and compare them. Fortunatly the backend contains functions that do all this already. Check out parser/parse_oper.c, in particular oper() and compatable_oper(). Note that this still leaves the question of what operator to search for, and where to look for it. The current system doesn't really provide an adequate way of identifying a suitable equality operator; you kind of have to take it on faith that people won't have made = do unexpected things (an assumption already violated by some builtin datatypes ...). We've been moving gradually in the direction of relying on btree operator classes to give us a better understanding of which operators really act like equality, but it's far from all done. The most recent thread about fixing this was http://archives.postgresql.org/pgsql-hackers/2006-02/msg00960.php Nothing much has been done since then as far as fixing foreign-key checks, but you might want to look at the code for interpreting row value comparisons (make_row_comparison_op in parse_expr.c). SelectSortFunction in tuplesort.c is another example of looking for btree info to infer the behavior of an operator. regards, tom lane -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html ---(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: [HACKERS] Scan Keys
Tom Lane [EMAIL PROTECTED] writes: I tried just using index_getprocinfo(...,BTORDER) with InvalidStrategy like btree does but _bt_preprocess_keys runs into problems without a valid strategy number. And in any case that would be btree specific which seems like it ought not be necessary. There's no particularly good reason to suppose that a non-btree index necessarily supports equality probes at all. For instance if you're using GIST or GIN for full-text searching, the index might only know about component words, not the whole strings that are in the table. Opclasses designed for spatial databases might conceivably not have equality either (though that's a bit more of a stretch). AFAIK even GIST indexes can fetch me a reasonably limited list of tuples that might be equal. It might include significantly more tuples than just what I'm looking for but it's much better than doing a full index scan. But on that note, is it ok to use the bulkdelete index AM methods for non-vacuum purposes as long as there's only one such process running at a time? Presumably that means taking an ShareUpdateExclusiveLock on the indexrelation or a ExclusiveLock on the pg_index line? As Martijn pointed out, we rely on btree-opclass equality as the main means of deciding what equality really is. I don't think it'd be wise to insist that every index opclass, no matter what its purpose, has to include an equality operator. I'm having trouble picturing any useful index where there's no operator close to equality. But I'll concede that that may be a failure of my imagination :) -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] UUID's as primary keys
[EMAIL PROTECTED] writes: e) Generic n-byte binary data type generator. Not sure of feasibility of this at this point. See thread. I don't like the idea of a generator that would have to be manually invoked, though such a thing would be a fine tool for contrib or pgfoundry, I think it would never be a clean enough interface for core. On the other hand core could conceivably translate things like char(n) into such a type generated on the fly. That is, instead of having a single char oid it could check a cache of fixed length char(n) data types and if there isn't one already generate one on the fly. That would be somewhat grotty of an implementation but the user interface at least would be entirely transparent. If one day we change things to pass around typmod database designs wouldn't have to change at all. (Actually Postgres can never do this for char(n), at least not as long as we insist on making char/varchar/text locale-aware. Personally I think the default char/varchar/text locale should be C unless you specify otherwise on a per-column basis. But that seems to be a minority opinion. Postgres could however do this for separate raw binary datatypes like bit(n) or bytea(n).) In answer to your question, though my opinion carries no special weight at all, I would suggest adding a bare bones 16-byte data type to core and a second binary-compatible data type based on it that parsed/output as uuids. The extended uuid libraries should only go in pgfoundry/contrib. -- greg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] UUID's as primary keys
Hi, Just MHO: 1) The added 128-bit type should take the form of: c) UUID, with only encode/decode/indexable - generic except for the name of the type, and the encoding format. 2) According to your answer in 1), the added 128-bit type should be: a) In core first. 1c is what I would need. 1b or 1a would be nice to have. - Sander ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Help with casting and comparing.
On Thu, Jul 06, 2006 at 07:43:20PM +0300, Tzahi Fadida wrote: The downside is that i noticed that the CTID is removed from the tuple if a cast occurs. Is there a way to tell postgresql to not remove the CTID? Err, the fact the ctid is removed is really just a side-effect. With no adjusting of the output, you may just get the actual on-disk tuple. But as soon as you do some manipulation, you get a new tuple. The other way, of course is to add CTID as an attribute in the query but it seems less efficient since i am accessing it repeatedly. If you want the ctid, you have to ask for it. But this seems a little like premature optimisation. First, make it work, then make it fast. Once you've got it working you can worry about performance. Adding an extra column to the output costs very, very little compared to other things... -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Help with casting and comparing.
On Thursday 06 July 2006 21:55, Martijn van Oosterhout wrote: On Thu, Jul 06, 2006 at 07:43:20PM +0300, Tzahi Fadida wrote: The downside is that i noticed that the CTID is removed from the tuple if a cast occurs. Is there a way to tell postgresql to not remove the CTID? Err, the fact the ctid is removed is really just a side-effect. With no adjusting of the output, you may just get the actual on-disk tuple. But as soon as you do some manipulation, you get a new tuple. The other way, of course is to add CTID as an attribute in the query but it seems less efficient since i am accessing it repeatedly. If you want the ctid, you have to ask for it. But this seems a little like premature optimisation. First, make it work, then make it fast. Once you've got it working you can worry about performance. Adding an extra column to the output costs very, very little compared to other things... It works, i use this technique for index accesses. I am not worried about getting this to work since i already tried this. I am more worried about optimization. Well, it is probably the lesser evil of dealing with casting. P.s. the code is running and can be found here: http://pgfoundry.org/projects/fulldisjunction/ -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] UUID's as primary keys
Greg Stark wrote: In answer to your question, though my opinion carries no special weight at all, I would suggest adding a bare bones 16-byte data type to core and a second binary-compatible data type based on it that parsed/output as uuids. The extended uuid libraries should only go in pgfoundry/contrib. I second that. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Scan Keys
Greg Stark [EMAIL PROTECTED] writes: But on that note, is it ok to use the bulkdelete index AM methods for non-vacuum purposes Um, what would those be? ambulkdelete and amvacuumcleanup are most certainly not designed to be used in any context other than VACUUM. You might be able to abuse them for some other purpose, but don't expect a warranty. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] request for feature: psql DSN option
Per tonight's dinner discussion, it Sure Would Be Nice if psql had an
additional option that allowed passing in a conninfo string.
The notion: Plenty of libraries out there like Pg, DBI::Pg, and such
make you specify connections in the form:
host=my.db.host.example.org port=5678 dbname=dumb_db user=chewie
Since those strings need to be kept around for the programs that are
directly using conninfo, it Sure Would Be Nice if those strings could
also be used as arguments to psql.
Peter Eisentraut observed that -D is not yet used; that's certainly
an option.
Thus, supposing we kept conninfo strings in a series of files in
/opt/scripts/conninfo, we might run a script via...
#!/bin/sh
CONNINFO=`cat /opt/scripts/conninfo/dumb_db.conninfo`
PERIOD='90 days'
QUERY=delete from my_table where trans_on now() - '${PERIOD}'::interval;
QUERY2=vacuum analyze my_table;
psql -D ${CONNINFO} -c ${QUERY}
psql --pqconninfo=${CONNINFO} -c ${QUERY2}
I'm not sure those are forcibly the best names for options, but
they're a thought...
--
select 'cbbrowne' || '@' || 'gmail.com';
http://linuxdatabases.info/info/
Rules of the Evil Overlord #10. I will not interrogate my enemies in
the inner sanctum -- a small hotel well outside my borders will work
just as well. http://www.eviloverlord.com/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[HACKERS] xlog viewer prototype and new proposal
I've worked on a prototype (attached to this email) of the SRF function and I can query the xlog files for some useful info.I know that the error codes are still incorrect and the tests are missing, but this is only a proof of concept.
Examples of usage:Query for committed transactions on the xlog segment:postgres=# SELECT * from xlogviewer('/usr/local/pgsql/data/pg_xlog/00010003') where trim(infoname) = 'commit';
rmid | xid | rmname | info | len | total_len | infoname--+-++--+-+---+-- 1 | 4 | XACT | 0 | 12 | 40 | commit 1 | 5 | XACT | 0 | 12 | 40 | commit
...Query for the total length of transactions in the segment:postgres=# SELECT xid, sum(total_len) from xlogviewer('/usr/local/pgsql/data/pg_xlog/00010003') group by xid;xid | sum
-+-499 | 69054497 | 1460...I'll sumarize some relevant points of our previous thread (can be viewed at: http://archives.postgresql.org/pgsql-hackers/2006-06/msg01069.php
):I exposed the idea of bringing the xlogdump functionality to a backend module.The main drawback is the use case where the database is down.But the access to a failed cluster isn't impossible, just a little bit more dificult, requiring another cluster to be initialized.
I don't think that this is a no-go for the design, of course for the case where the database is down is still better to have a standalone tool.So there were suggestions to code something that could be used both on the frontend and the backend.
Tom said it would be difficult to use the same functions on backend and frontend.I think that I could use the same library and pass pointers to memory allocation and error reporting functions.Advantages in the SRF design:
- Using SQL to agregate and transform data in any way from the logs.- It's easier for the DBA in the other use cases where the cluster is still active.- I already have a database connection in cases where I want to translate oid to names.
- I can connect directly to the postgresql server if I want to query xlogs in a remote machine (don't need remote access to the system).- Easier to integrate with existing admin tools, like PgAdmin.In any case I need to start ASAP, to have something useful till the end of the google soc.
So if the way to go will be the standalone program, I think that I can enhance it by adding:- option to translate OIDs to names given a database connection- find loser transactions (not commited to the end of the log)
- have an options to output only the transactions with their status and some aggregate data (transaction size).- a CSV output (to read it's data on another programs, including the backend)There's one functionality I would like to work on but I don't how dificult it would be to acomplish in time:
- Extract the exact operation done in cases of xlog generated by insert/update/delete.-- Diogo Biazus - [EMAIL PROTECTED]Móvel Consultoria
http://www.movelinfo.com.brhttp://www.postgresql.org.br
xlogviewer.tar.gz
Description: GNU Zip compressed data
---(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
