Re: [HACKERS] Do we really want to migrate plproxy and citext into PG core distribution?
On Thu, Jul 24, 2008 at 2:37 PM, Tom Lane [EMAIL PROTECTED] wrote: Hannu Krosing [EMAIL PROTECTED] writes: And it would be nice, if some well-maintained sample language (pl/sh or even pl/dummy) which serves as a sample of latest ways to make use of pl/function support in core pg code would be included in core as well. And why do you think the above three don't serve that purpose? Or even more to the point, how likely is it that an unused dummy language would be well-maintained? For whatever it's worth, I'm in the middle of writing a PL (PL/LOLCODE, specifically), and have found helpful examples of how to do stuff in PL/pgSQL, PL/Perl, *and* pl/proxy. The examples in the documentation followed by a bunch of hair pulling while reading PL/pgSQL were enough to get started, without the benefit of a dummy language. That's not to say that a dummy language wouldn't be useful, only that for a coder of my caliber (i.e. Not Terribly Skilled but Able to Code Myself Out of a Wet Paper Bag) it wasn't necessary. Because pl/proxy is not in core, I didn't immediately look to it for examples, but was pointed there by a helpful soul on IRC. My own opinion is that though there have been several in recent years, new PLs are written rarely enough that best practices don't change a whole lot. PL/Perl and PL/pgSQL particularly are very well maintained, and thus demonstrate in most cases a perfectly acceptable way of writing a PL. As to whether or not pl/proxy should be in core, I have no particular opinion. PL/LOLCODE probably should not be. :) - Josh / eggyknap -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem returning strings with pgsql 8.3.x
On Tue, May 13, 2008 at 8:01 AM, Tom Lane [EMAIL PROTECTED] wrote:
Martijn van Oosterhout [EMAIL PROTECTED] writes:
On Mon, May 12, 2008 at 11:23:17PM -0600, Josh Tolley wrote:
SPI_push();
retval =
InputFunctionCall(flinfo, lolVarGetString(returnVal, true),
resultTypeIOParam, -1);
SPI_pop();
Won't this cause the return value to be allocated inside a new memory
block which gets freeds at the SPI_pop?
The SPI_pop in itself is harmless ... the problem is the SPI_finish
further down, which will release all simple palloc's done within the
SPI function context. What he needs is something comparable to this bit
in plpgsql:
/*
* If the function's return type isn't by value, copy the value
* into upper executor memory context.
*/
if (!fcinfo-isnull !func-fn_retbyval)
{
Sizelen;
void *tmp;
len = datumGetSize(estate.retval, false, func-fn_rettyplen);
tmp = SPI_palloc(len);
memcpy(tmp, DatumGetPointer(estate.retval), len);
estate.retval = PointerGetDatum(tmp);
}
ie, push the data into something allocated with SPI_palloc().
I'll give this a shot as soon as I can... many thanks
I would bet large amounts of money that the problem is not new in
8.3.0, either. Perhaps Josh was not testing in an --enable-cassert
(CLOBBER_FREED_MEMORY) build before.
I'll check... that's definitely not unlikely. Again, thanks.
- Josh
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem returning strings with pgsql 8.3.x
On Tue, May 13, 2008 at 8:19 AM, Josh Tolley [EMAIL PROTECTED] wrote:
On Tue, May 13, 2008 at 8:01 AM, Tom Lane [EMAIL PROTECTED] wrote:
Martijn van Oosterhout [EMAIL PROTECTED] writes:
On Mon, May 12, 2008 at 11:23:17PM -0600, Josh Tolley wrote:
SPI_push();
retval =
InputFunctionCall(flinfo, lolVarGetString(returnVal, true),
resultTypeIOParam, -1);
SPI_pop();
Won't this cause the return value to be allocated inside a new memory
block which gets freeds at the SPI_pop?
The SPI_pop in itself is harmless ... the problem is the SPI_finish
further down, which will release all simple palloc's done within the
SPI function context. What he needs is something comparable to this bit
in plpgsql:
/*
* If the function's return type isn't by value, copy the value
* into upper executor memory context.
*/
if (!fcinfo-isnull !func-fn_retbyval)
{
Sizelen;
void *tmp;
len = datumGetSize(estate.retval, false,
func-fn_rettyplen);
tmp = SPI_palloc(len);
memcpy(tmp, DatumGetPointer(estate.retval), len);
estate.retval = PointerGetDatum(tmp);
}
ie, push the data into something allocated with SPI_palloc().
I'll give this a shot as soon as I can... many thanks
I would bet large amounts of money that the problem is not new in
8.3.0, either. Perhaps Josh was not testing in an --enable-cassert
(CLOBBER_FREED_MEMORY) build before.
I'll check... that's definitely not unlikely. Again, thanks.
- Josh
Proper (I hope) use of SPI_palloc() took care of this. And yes, the
8.2.x version I was using without problem was compiled without
enable-cassert. Once again, thanks.
- Josh / eggyknap
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Problem returning strings with pgsql 8.3.x
Having posted this to -general [1] per -hackers list instructions [2]
to try elsewhere first, and waited (not very long, I admit) in vain
for a response, I'm posting this to -hackers now. My apologies if my
impatience in that regard annoys.
While developing PL/LOLCODE, I've found something wrong with returning
strings from LOLCODE functions using 8.3.0 or greater. Using 8.4beta
from a few days ago, for instance, a function that should return test
string returns
\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F in
pgsql (sometimes the number of \x7F characters varies). In 8.2.4 it
works fine.
Here's the code involved, from pl_lolcode_call_handler, the call
handler function for PL/LOLCODE. First, the bit that finds the
FmgrInfo structure and typioparam for the result type:
procTup = SearchSysCache(PROCOID,
ObjectIdGetDatum(fcinfo-flinfo-fn_oid), 0, 0, 0);
if (!HeapTupleIsValid(procTup)) elog(ERROR, Cache lookup
failed for procedure %u, fcinfo-flinfo-fn_oid);
procStruct = (Form_pg_proc) GETSTRUCT(procTup);
typeTup = SearchSysCache(TYPEOID,
ObjectIdGetDatum(procStruct-prorettype), 0, 0, 0);
if (!HeapTupleIsValid(typeTup)) elog(ERROR, Cache lookup
failed for type %u, procStruct-prorettype);
typeStruct = (Form_pg_type) GETSTRUCT(typeTup);
resultTypeIOParam = getTypeIOParam(typeTup);
fmgr_info_cxt(typeStruct-typinput, flinfo,
TopMemoryContext); /*CurTransactionContext); */
ReleaseSysCache(typeTup);
Here's the code that converts the return value into a Datum later on
in the function:
if (returnTypeOID != VOIDOID) {
if (returnVal != NULL) {
if (returnVal-type == ident_NOOB)
fcinfo-isnull = true;
else {
SPI_push();
if (returnTypeOID == BOOLOID)
retval =
InputFunctionCall(flinfo, lolVarGetTroof(returnVal) == lolWIN ?
TRUE : FALSE, resultTypeIOParam, -1);
else {
/* elog(NOTICE,
lolVarGetString(returnVal, true)); */
retval =
InputFunctionCall(flinfo, lolVarGetString(returnVal, true),
resultTypeIOParam, -1);
}
SPI_pop();
}
}
else {
fcinfo-isnull = true;
}
}
SPI_finish();
/* elog(NOTICE, PL/LOLCODE ending); */
return retval;
returnVal is an instance of the struct PL/LOLCODE uses to store its
variables. The key line in this case is the one after the
commented-out call to elog. retval is a Datum type. lolVarGetString()
returns the string value the returnVal struct represents -- I'm
certain of that thanks to gdb and other testing. All other data types
PL/LOLCODE knows about internally seem to return just fine. I'm fairly
certain I'm screwing up memory somewhere, but I can't see what I've
done wrong.
I'm glad to provide further details, but those included above are all
the ones I thought were relevant. Thanks in advance for any help you
can provide.
- Josh / eggyknap
[1] http://archives.postgresql.org/pgsql-general/2008-05/msg00311.php
[2] http://archives.postgresql.org/pgsql-hackers/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] relations diagram of tables in the catalog system
On 10/23/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, i am new in this, and i need help with catalog system of postgresql, i need know how are the relationship between the tables of the system catalog, and how work each table, if anybody know about this please, answer me. thank all of you. The documentation is your friend. Refer to http://www.postgresql.org/docs/8.2/interactive/catalogs.html for catalog table information specifically. - Josh/eggyknap ---(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] A Silly Idea for Vertically-Oriented Databases
On 9/7/07, Avery Payne [EMAIL PROTECTED] wrote: Avery, my ramblings snipped If someone writes the rest of the code, I doubt the syntax will be the holdup. But writing an efficient C-store table mechanism is much harder than I think you think it is; Vertica worked on it for a year and failed, and Paraccel took two years to succeed. FYI, Paraccel is based on Postgres. So, put up a pgfoundry project and start hacking a c-store table; I'm sure you;ll get interest if you can make something work. -- --Josh Well, I did say it was a *crazy* idea. :-) Given that I would be starting from the ground-floor, learning not only the innards of PostgreSQL but also C coding as well, I would probably have to overcome near-insurmountable odds to make this project take off. Still, if I was crazy enough to think it, maybe I'll be crazy enough to try for it. ;-) Just ignore my 2nd posting, I was trying to clarify some of the ramblings I was typing. For whatever it's worth, I was reading about the same things today and came up with the same basic idea, without the same level of implementation details you've talked about, Avery. And it sounds really neat. Hard, but neat, and potentially worth it if, say, Paraccel doesn't open source their stuff first :) But I don't know the PostgreSQL internals either, though I've been known to throw together some C code now and again. So in short, there's interest. Whether there's collective skill/free time/motivation/insanity/etc. enough to make something useful of the interest is another question altogether. :) - Josh/eggyknap ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Per-function search_path = per-function GUC settings
On 9/1/07, Tom Lane [EMAIL PROTECTED] wrote: Jeff Davis [EMAIL PROTECTED] writes: Can we also provide syntax which would be equivalent to setting var for the function to be whatever the current value happens to be when the ALTER FUNCTION is run? Possible syntax might be something like: ALTER FUNCTION func(args) SET var TO CURRENT; Hmmm ... that's certainly do-able, though I'm not sure how much it helps the use-case you suggest. The search path still has to be set at the top of the module script, no? However, I like an explicit option of this sort a lot better than the automatic version Greg was suggesting ... I'm willing to do it if people want it. One problem is that we'd have to make CURRENT a reserved word to make it work exactly like that. Can anyone think of a variant syntax that doesn't need a new reserved word? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster +1 for being able to define the entire function in one command, +1 for not inheriting a bunch of GUC settings without the definer's explicit say-so. - Josh/Eggyknap ---(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] how to pg_dump, based in select command
On 7/3/07, Jeferson Kasper [EMAIL PROTECTED] wrote: Hello folks. I want to know how to dump some parts of a database? I need to extract the records in a select * from table and the pg_dump or other tool will create a file with the records found in this select command, and after this, i will restore this file in another database with the same structure. Any idea? Thanks people. Jeferson Kasper In 8.2 you can use the COPY command with a query, as in COPY (SELECT ...) TO 'filename' In earlier versions, one alternative is to create a table filled with the results of your query and pg_dump it. Alternatively you might also pipe the query into psql and pipe the output to a file, use psql's \o option, etc. -Josh ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] EXPLAIN omits schema?
On 6/13/07, Heikki Linnakangas [EMAIL PROTECTED] wrote: Magnus Hagander wrote: On Wed, Jun 13, 2007 at 02:02:24PM +0100, Heikki Linnakangas wrote: DB2 has the concept of explain tables. Explain output is written to tables, which tools query and pretty print the output. I like that idea in principle. PostgreSQL is a relational database, so having the explain output in relations make sense. No need for XML or any other extra libraries, in either the server or client. Having the data in relational format allows you to query them. For example, show me all sequential scans, or all nodes where the estimated number of rows is off by a certain factor. Assuming you can actually *represent* the whole plan as tables, that would of course work fine. Sure you can. It's just a question of how complex the schema is :). But I assume you mean virtual tables? So I do EXPLAIN whatever, and get back one or more resultssets with the data? Or do they write it to *actual* tables in the database? I'm not sure. DB2 had real tables, but I found that a bit clumsy. It was nice because your old explain results were accumulated, but it was also not nice because of that same thing. One idea would be temporary tables. Machine-readable is of course the main point - the exact format is more of an implementation detail. Agreed. A potential problem is that as we add new node types etc., we need to extend the schema (whether it's a real relational schema or XML), and clients need to understand it. But I guess we already have the same problem with clients that parse the current explain output. Oracle forces you (AFAIK) to create a set of tables to store explain plan output, so when you EXPLAIN, it populates those tables, and then you have to query to get it out. This is nice for admin tools that have to parse the explain output, though it's obviously a pain for explain-ing inside a command-line. An XML explain would be neat. On a different sideline based on the original note of this thread, much as EXPLAIN doesn't include the schema, \d doesn't include the schema to describe INHERIT relationships in 8.2.4. If you have two tables called PARENT, in two different schemas, and a child that inherits from one of them, \d won't tell you which of the two it inherits from. - Josh ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] \d omits schema on inherited tables (Was: EXPLAIN omits schema?)
On 6/13/07, Tom Lane [EMAIL PROTECTED] wrote: Josh Tolley [EMAIL PROTECTED] writes: On a different sideline based on the original note of this thread, much as EXPLAIN doesn't include the schema, \d doesn't include the schema to describe INHERIT relationships in 8.2.4. If you have two tables called PARENT, in two different schemas, and a child that inherits from one of them, \d won't tell you which of the two it inherits from. Yes it does, because that's actually regclass output. It'll be schema-qualified if the table is not visible in your search path. I figured it was better to start a new thread, since this changes from the original topic. My test didn't display the schema despite the parent not being in my search path, as shown below: [EMAIL PROTECTED] ~]$ psql Welcome to psql 8.2.4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit jtolley=# create schema a; CREATE SCHEMA jtolley=# create schema b; CREATE SCHEMA jtolley=# set search_path to a, public; SET jtolley=# create table parent (f int); CREATE TABLE jtolley=# set search_path to b, public; SET jtolley=# create table parent (g text); CREATE TABLE jtolley=# create table child () inherits (a.parent); CREATE TABLE jtolley=# \d child Table b.child Column | Type | Modifiers +-+--- f | integer | Inherits: parent jtolley=# \d parent Table b.parent Column | Type | Modifiers +--+--- g | text | jtolley=# \d a.parent Table a.parent Column | Type | Modifiers +-+--- f | integer | jtolley=# set search_path to b; SET jtolley=# \d child Table b.child Column | Type | Modifiers +-+--- f | integer | Inherits: parent jtolley=# set search_path to a; SET jtolley=# \d b.child Table b.child Column | Type | Modifiers +-+--- f | integer | Inherits: parent ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Fate of pgsnmpd
On 4/6/07, Dave Page [EMAIL PROTECTED] wrote: Yes, it is. There have been a number of commits recently and I believe Josh(?) is giving a talk about it at pgCon. Regards, Dave Josh just found his passport, which will make giving that talk a lot easier ;) As Magnus said, we're aiming at RFC 1697 compliance first. Since the RFC's MIB is designed to apply to *any* database, it doesn't cover lots of the specific statistics a pgsql person would likely want to see, so after the RFC work is done we'll be adding a pgsql-specific MIB.. - Josh Tolley ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Fate of pgsnmpd
On 4/7/07, Josh Berkus josh@agliodbs.com wrote: FYI, the MySQL folks want to talk to you about maybe lobbying to change the RFC. They feel that an awful lot of RFC1697 is Oracle-specific, and are wondering if we can do anything about it. Indeed... I've had brief discussions with a Mark Atwood, IIRC, who's working on the MySQL implementation. I'll drop them a line, and cc pgsnmpd-devel. Thanks. - Josh Tolley ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
