Re: [HACKERS] ruby connect
Hi, I’m trying to connect ruby to postgres on ubuntu and the only link I found that has the library is down. Does anyone have the postgres library for ruby? Or direct me to it? You would be better off searching for the drivers on rubyforge.org. Please do not send non-Postgres-development related queries on this list. Regards, Nikhils -- http://www.enterprisedb.com
Re: [HACKERS] pg_migrator and an 8.3-compatible tsvector data type
Greg Stark wrote: On Sat, May 30, 2009 at 1:11 PM, Bruce Momjian br...@momjian.us wrote: I have discovered a simpler solution using ALTER TABLE and calling a conversion function: ? ? ? ?test= CREATE TABLE tsvector_test(x tsvector); ? ? ? ?CREATE TABLE ? ? ? ?test= ALTER TABLE tsvector_test ALTER COLUMN x TYPE tsvector ? ? ? ?test- USING conversion_func(x); ? ? ? ?ALTER TABLE No need for a fake data type and the required index infrastructure. I assume you're putting this in the list of commands to run post-migration along with any reindex commands etc? Because it will take a while (still faster than dump/reload i think). For this case, assuming the new tsvector's output function doesn't get confused by the old ordering, I think you can just use USING x::text::tsvector as your conversion expression. For more complex cases you might need to package up the old output function. Also note that you'll want to do any other conversions in the same table at the same time rather than doing multiple conversions. I have implemented Greg's ideas using ::text::tsvector casting and it worked: $ psql test psql (8.4beta2) Type help for help. test= select * from tsvector_test; x 'c' 'bb' 'aaa' (1 row) test= test= \q $ psql test /u/postgres/rebuilt_tsvector_tables.txt ALTER TABLE ALTER TABLE ALTER TABLE $ psql test psql (8.4beta2) Type help for help. test= select * from tsvector_test; x 'aaa' 'bb' 'c' (1 row) Here is the script that groups multiple tsvector columns into a single ALTER TABLE: ALTER TABLE public.tsvector_test ALTER COLUMN x TYPE tsvector USING x::text::tsvector; ALTER TABLE public.tsvector_test2 ALTER COLUMN x TYPE tsvector USING x::text::tsvector, ALTER COLUMN y TYPE tsvector USING y::text::tsvector; ALTER TABLE public.tsvector_test3 ALTER COLUMN x TYPE tsvector USING x::text::tsvector, ALTER COLUMN y TYPE tsvector USING y::text::tsvector, ALTER COLUMN z TYPE tsvector USING z::text::tsvector; I knew I was going to have to deal with this optimization but didn't realize ALTER TABLE already supported multiple TYPE changes. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] pg_migrator and an 8.3-compatible tsvector data type
Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I have discovered a simpler solution using ALTER TABLE and calling a conversion function: test= CREATE TABLE tsvector_test(x tsvector); CREATE TABLE test= ALTER TABLE tsvector_test ALTER COLUMN x TYPE tsvector test- USING conversion_func(x); ALTER TABLE No need for a fake data type and the required index infrastructure. I think this is basically a large-caliber foot gun. You're going to pretend that invalid data is valid, until the user gets around to fixing it? What choice do we have? While we can mark indexes as invalid (which we do), how do we mark a table's contents as invalid? Should we create rules so no one can see the data and then have the ALTER TABLE script remove the rules after it is rebuilt? OK, what ideas do people have to prevent access to tsvector columns? I am thinking of renaming the tables or something. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] pg_migrator and an 8.3-compatible tsvector data type
On Sun, May 31, 2009 at 3:04 PM, Bruce Momjian br...@momjian.us wrote: I think this is basically a large-caliber foot gun. You're going to pretend that invalid data is valid, until the user gets around to fixing it? What choice do we have? I think in this case the caliber is pretty small and this might be sufficient. It might be nice if we had a check somewhere in the tsvector data types so people get informative errors if their tsvectors are old-style rather than random incorrect results, but that's mostly gilding. In the general case of data type representation changes I think we need something like: While we can mark indexes as invalid (which we do), how do we mark a table's contents as invalid? Should we create rules so no one can see the data and then have the ALTER TABLE script remove the rules after it is rebuilt? OK, what ideas do people have to prevent access to tsvector columns? I am thinking of renaming the tables or something. 1 Change the catalog so all the tsvector colums are bytea. 2 Include a c function like migrate_tsvector(bytea) which contains a copy of the old data type's output function and calls the new data type's input function on the result. 3 Include an ALTER TABLE command which calls the c function. The gotchas I can see with this is: 1) It only works for varlenas -- There isn't a universal fixed length data type. You would probably have to invent one. 2) I'm not sure what will happen to rules and triggers which call functions on the old data type. If you restore the schema unchanged and modify the catalog directly then they will still be there but have mismatched types. Will users get errors? Will those errors be sensible errors or nonsensical ones? Will the conversion still go ahead or will it complain that there are things which depend on the column? If the problems in (2) prove surmountable then this provides a general solution for any varlena data type representation change. However it will still be a O(n) conversion plus an index rebuild. That's unfortunate but unless we plan to ship the full set of operators, opclasses, opfamilies, cross-data-type operators, etc for the old data type I see no way around it. I haven't heard anyone suggest we should roll back the tsvector changes and give up the features the changes provide -- and that's just a performance feature. If that's all it took to convince us to give up in-place-upgrade for this data type then imagine how easy it will be to justify for actual functional features. (Personally I think we're fooling ourselves to think Postgres is mature enough that we won't come up with any new improvements which will justify a data format change. I would rather hope we'll keep coming up with massive improvements which require major changes in every release.) -- greg -- 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] information_schema.columns changes needed for OLEDB
Peter Eisentraut pete...@gmx.net writes: On Friday 22 May 2009 18:27:01 Konstantin Izmailov wrote: [ discussion of some details of information_schema results ] Are we going to do anything about any of these issues? In particular, 6. datetime_precision field, minor changes Code around: change value on the fly, e.g. [date] set value to zero Makes sense. I think this is not correct at the moment. AFAICS, the SQL standard demands that precision and scale fields be non-null all the time for those data types where they make sense (this is encoded in the CHECK CONSTRAINTs that are declared for the various information-schema tables, see particularly 21.15 DATA_TYPE_DESCRIPTOR base table in SQL99). DATE is clearly wrong per spec, but it's not the only problem. Our interpretation has been to set these values to null if the typmod is defaulted, which is reasonable in the abstract but it's still a violation of spec. I wonder whether we should be inserting some large limit value instead. For the datetime types we actually have a perfectly good default precision value, namely six digits, if the DB is using integer datetimes --- and I don't think there's a strong argument not to use this value for float timestamps too. I'd prefer to avoid a catversion bump at this stage of the cycle, but it looks like any changes here would merely involve the bodies of some functions in information_schema.sql. I think we could just change them without a catversion bump. Any beta testers who actually care could easily insert the new definitions without an initdb. regards, tom lane -- 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] Feedback on writing extensible modules
Dimitri Fontaine dfonta...@hi-media.com writes: Dimitri Fontaine dfonta...@hi-media.com writes: And currently calling SPI_connect() from _PG_init will crash the backend. I'll try to obtain a gdb backtrace, I've just been told about pre_auth_delay and post_auth_delay parameters. Here we go: The reason this doesn't work is that SPI can only be invoked inside a transaction, and you're not inside one when a library is being preloaded. I'm very interrested in being able to prepare a query at local_preload_libraries time, if possible in 8.3 and following releases. You could maybe make this work by executing your own transaction to do it, but I really have to wonder if it's a good idea. One point to think about is that elog(ERROR) still means elog(FATAL) at this point, so any brokenness in the queries you're trying to prepare will result in locking all users out of the database. regards, tom lane -- 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] [BUGS] BUG #4822: xmlattributes encodes '' twice
Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes: Here is a patch to fix the bug. I added a parameter 'encode' to map_sql_value_to_xml_value() and pass false for xml attributes. One thing I was wondering about, which is sort of highlighted by your patch, is why is there the special exception for XML type in the existing code, and how does that interact with this behavior? ! /* ... exactly as-is for XML or encode is not required */ ! if (type == XMLOID || !encode) return str; Seems like there could be cases where we're getting one too many or too few encoding passes when the input is XML. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] check for missing tablespaces?
I am in the middle of helping a customer recover from a situation where a tablespace was missing when a machine was rebooted and postgres restarted, and I'm wondering if we should not have some sort of check for this on startup. Maybe we could check for the existence of the PG_VERSION file or something like that? Of course, that wouldn't help if the tablespace were subsequently lost, but it doesn't seem like a terribly expensive or unreasonable sanity check for startup. cheers andrew -- 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] check for missing tablespaces?
* Andrew Dunstan (and...@dunslane.net) wrote: I am in the middle of helping a customer recover from a situation where a tablespace was missing when a machine was rebooted and postgres restarted, and I'm wondering if we should not have some sort of check for this on startup. Maybe we could check for the existence of the PG_VERSION file or something like that? Of course, that wouldn't help if the tablespace were subsequently lost, but it doesn't seem like a terribly expensive or unreasonable sanity check for startup. I agree entirely with doing this. Now that we've got tablespaces and our users are using them to split things on to seperate partitions which could be accessed in different ways (eg: a tablespace on local storage and one on a SAN) it makes alot of sense to make sure everything is in order before allowing the system to completely come up. I'd much rather PG not start when the SAN mappings aren't in place. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] check for missing tablespaces?
Andrew Dunstan and...@dunslane.net writes: I am in the middle of helping a customer recover from a situation where a tablespace was missing when a machine was rebooted and postgres restarted, and I'm wondering if we should not have some sort of check for this on startup. Maybe we could check for the existence of the PG_VERSION file or something like that? ... and do what? What exactly went wrong so badly with the missing tablespace? Doesn't seem like it should be any worse than if individual table files went missing. regards, tom lane -- 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] check for missing tablespaces?
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: I am in the middle of helping a customer recover from a situation where a tablespace was missing when a machine was rebooted and postgres restarted, and I'm wondering if we should not have some sort of check for this on startup. Maybe we could check for the existence of the PG_VERSION file or something like that? ... and do what? What exactly went wrong so badly with the missing tablespace? Doesn't seem like it should be any worse than if individual table files went missing. Well, in the present instance probably nothing drastic went wrong. The tablespace only contained indexes - if an index is missing then updates to the base table will fail, right? In general, I think I'd probably prefer normal database startup to fail if a tablespace is missing. That way I will know about it right then and can remedy it. This is something that is much more likely to happen than an arbitrary missing file, ISTM, and at the same time the check would be much easier than looking for an arbitrary file. At the very least we could issue a warning. cheers andrew -- 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] check for missing tablespaces?
Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: ... and do what? In general, I think I'd probably prefer normal database startup to fail if a tablespace is missing. That way I will know about it right then and can remedy it. This is something that is much more likely to happen than an arbitrary missing file, ISTM, and at the same time the check would be much easier than looking for an arbitrary file. At the very least we could issue a warning. So what you're imagining is * iterate through each symlink in $PGDATA/pg_tblspc * check that PG_VERSION exists (and has the right contents??) in each pointed-to directory * fail if not I guess this is reasonable, since we make a similar check for the core data directory itself. Watch out for the initdb sequence though. regards, tom lane -- 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] check for missing tablespaces?
Tom Lane wrote: So what you're imagining is * iterate through each symlink in $PGDATA/pg_tblspc * check that PG_VERSION exists (and has the right contents??) in each pointed-to directory * fail if not I guess this is reasonable, since we make a similar check for the core data directory itself. Watch out for the initdb sequence though. I'll put this on my TODO list. I'm not sure about the initdb reference - there won't be any tablespaces to check for during initdb, will there? cheers andrew -- 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] check for missing tablespaces?
Andrew Dunstan and...@dunslane.net writes: I'm not sure about the initdb reference - there won't be any tablespaces to check for during initdb, will there? No, but I think pg_tblspc/ itself might not be there either. Just a case to test your patch on ... regards, tom lane -- 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] check for missing tablespaces?
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: I'm not sure about the initdb reference - there won't be any tablespaces to check for during initdb, will there? No, but I think pg_tblspc/ itself might not be there either. Just a case to test your patch on ... Sure it is, it's set up by initdb along with the other subdirectories before it creates the PG_VERSION files. Anyway, I don't know that there's any urgency about this. cheers andrew -- 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] check for missing tablespaces?
Andrew Dunstan wrote: I am in the middle of helping a customer recover from a situation where a tablespace was missing when a machine was rebooted and postgres restarted, Have you uncovered why the tablespace when missing? and I'm wondering if we should not have some sort of check for this on startup +1 Andrew Chernow -- 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] check for missing tablespaces?
Andrew Dunstan wrote: I am in the middle of helping a customer recover from a situation where a tablespace was missing when a machine was rebooted and postgres restarted, Have you uncovered why the tablespace went missing? and I'm wondering if we should not have some sort of check for this on startup +1 Andrew Chernow -- 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] dblink patches for comment
Tom Lane wrote:
It's hard to review it without any docs that say what it's supposed to do.
(And you'd need to patch the docs anyway, eh?)
Fair enough :-)
Probably better if I break this up in logical chunks too. This patch
only addresses the refactoring you requested here:
http://archives.postgresql.org/message-id/28719.1230996...@sss.pgh.pa.us
I'll follow up later today with a SQL/MED only patch which includes docs.
Joe
Index: dblink.c
===
RCS file: /opt/src/cvs/pgsql/contrib/dblink/dblink.c,v
retrieving revision 1.77
diff -c -r1.77 dblink.c
*** dblink.c 1 Jan 2009 17:23:31 - 1.77
--- dblink.c 25 May 2009 22:57:22 -
***
*** 77,83
/*
* Internal declarations
*/
! static Datum dblink_record_internal(FunctionCallInfo fcinfo, bool is_async, bool do_get);
static remoteConn *getConnectionByName(const char *name);
static HTAB *createConnHash(void);
static void createNewConnection(const char *name, remoteConn * rconn);
--- 78,84
/*
* Internal declarations
*/
! static Datum dblink_record_internal(FunctionCallInfo fcinfo, bool is_async);
static remoteConn *getConnectionByName(const char *name);
static HTAB *createConnHash(void);
static void createNewConnection(const char *name, remoteConn * rconn);
***
*** 689,713
Datum
dblink_record(PG_FUNCTION_ARGS)
{
! return dblink_record_internal(fcinfo, false, false);
}
PG_FUNCTION_INFO_V1(dblink_send_query);
Datum
dblink_send_query(PG_FUNCTION_ARGS)
{
! return dblink_record_internal(fcinfo, true, false);
}
PG_FUNCTION_INFO_V1(dblink_get_result);
Datum
dblink_get_result(PG_FUNCTION_ARGS)
{
! return dblink_record_internal(fcinfo, true, true);
}
static Datum
! dblink_record_internal(FunctionCallInfo fcinfo, bool is_async, bool do_get)
{
FuncCallContext *funcctx;
TupleDesc tupdesc = NULL;
--- 707,753
Datum
dblink_record(PG_FUNCTION_ARGS)
{
! return dblink_record_internal(fcinfo, false);
}
PG_FUNCTION_INFO_V1(dblink_send_query);
Datum
dblink_send_query(PG_FUNCTION_ARGS)
{
! PGconn *conn = NULL;
! char *connstr = NULL;
! char *sql = NULL;
! remoteConn *rconn = NULL;
! char *msg;
! bool freeconn = false;
! int retval;
!
! if (PG_NARGS() == 2)
! {
! DBLINK_GET_CONN;
! sql = text_to_cstring(PG_GETARG_TEXT_PP(1));
! }
! else
! /* shouldn't happen */
! elog(ERROR, wrong number of arguments);
!
! /* async query send */
! retval = PQsendQuery(conn, sql);
! if (retval != 1)
! elog(NOTICE, %s, PQerrorMessage(conn));
!
! PG_RETURN_INT32(retval);
}
PG_FUNCTION_INFO_V1(dblink_get_result);
Datum
dblink_get_result(PG_FUNCTION_ARGS)
{
! return dblink_record_internal(fcinfo, true);
}
static Datum
! dblink_record_internal(FunctionCallInfo fcinfo, bool is_async)
{
FuncCallContext *funcctx;
TupleDesc tupdesc = NULL;
***
*** 775,788
/* shouldn't happen */
elog(ERROR, wrong number of arguments);
}
! else if (is_async do_get)
{
/* get async result */
if (PG_NARGS() == 2)
{
/* text,bool */
DBLINK_GET_CONN;
! fail = PG_GETARG_BOOL(2);
}
else if (PG_NARGS() == 1)
{
--- 815,828
/* shouldn't happen */
elog(ERROR, wrong number of arguments);
}
! else /* is_async */
{
/* get async result */
if (PG_NARGS() == 2)
{
/* text,bool */
DBLINK_GET_CONN;
! fail = PG_GETARG_BOOL(1);
}
else if (PG_NARGS() == 1)
{
***
*** 793,929
/* shouldn't happen */
elog(ERROR, wrong number of arguments);
}
- else
- {
- /* send async query */
- if (PG_NARGS() == 2)
- {
- DBLINK_GET_CONN;
- sql = text_to_cstring(PG_GETARG_TEXT_PP(1));
- }
- else
- /* shouldn't happen */
- elog(ERROR, wrong number of arguments);
- }
if (!conn)
DBLINK_CONN_NOT_AVAIL;
! if (!is_async || (is_async do_get))
{
! /* synchronous query, or async result retrieval */
! if (!is_async)
! res = PQexec(conn, sql);
! else
{
- res = PQgetResult(conn);
- /* NULL means we're all done with the async results */
- if (!res)
- {
- MemoryContextSwitchTo(oldcontext);
- SRF_RETURN_DONE(funcctx);
- }
- }
-
- if (!res ||
- (PQresultStatus(res) != PGRES_COMMAND_OK
- PQresultStatus(res) != PGRES_TUPLES_OK))
- {
- dblink_res_error(conname, res, could not execute query, fail);
- if (freeconn)
- PQfinish(conn);
MemoryContextSwitchTo(oldcontext);
SRF_RETURN_DONE(funcctx);
}
! if (PQresultStatus(res) == PGRES_COMMAND_OK)
! {
! is_sql_cmd = true;
!
! /* need a tuple descriptor representing one TEXT column */
! tupdesc = CreateTemplateTupleDesc(1, false);
! TupleDescInitEntry(tupdesc, (AttrNumber) 1, status,
! TEXTOID,
Re: [HACKERS] check for missing tablespaces?
Andrew Chernow wrote: Andrew Dunstan wrote: I am in the middle of helping a customer recover from a situation where a tablespace was missing when a machine was rebooted and postgres restarted, Have you uncovered why the tablespace went missing? No. It's on a SAN, and I understand our hardware guys are talking to the SAN vendor to get to the bottom of it. The network in the data centre was reconfigured last night, but that should not in fact have affected the SAN AFAIK. Other SAN devices mounted quite happily, including the one containing the main data directory. Anyway, from this POV all we really need to know is that the device hosting this tablespace failed to mount when the machine was rebooted, and then postgres restarted. cheers andrew -- 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] Feedback on writing extensible modules
Hi, First, thank you to have taken the time to see about the case. Le 31 mai 09 à 18:21, Tom Lane a écrit : The reason this doesn't work is that SPI can only be invoked inside a transaction, and you're not inside one when a library is being preloaded. Makes sense. Still crashing with basic naive testing, will report back when I have more time to work on it. You could maybe make this work by executing your own transaction to do it, but I really have to wonder if it's a good idea. One point to think about is that elog(ERROR) still means elog(FATAL) at this point, so any brokenness in the queries you're trying to prepare will result in locking all users out of the database. Yeah that's a pretty good foot gun, yet another one. But preprepare.at_init is optional and defaults to off. If you broke it all, you can turn it off again and reload. As for the FATAL, I guess that having preprepare crashing backend creations rather than having your EXECUTE fail and ROLLBACK your transactions is not so much a difference when you need preprepare in the first place... I'll add a note in the documentation to always manually call SELECT prepare_all() at each prepare statements list modification before to turn at_init on, as soon as at_init is possible. Regards, -- dim -- 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] Dtrace probes documentation
Joshua Tolley eggyk...@gmail.com writes: On Thu, May 28, 2009 at 06:28:14PM -0400, Tom Lane wrote: Read 26.4.3 and .4. I don't know why they have this bizarre set of conventions, but the single-hyphen version is the spelling most visible to end users. I thought it might be something like that. I've been playing with SystemTap, and found that only the double-underscore version works for ... well, anything. See http://blog.endpoint.com/2009/05/postgresql-with-systemtap.html for details. Perhaps it's worth noting in the documentation that SystemTap users will need to use the double-underscore version? I think a better solution is to persuade the Systemtap guys that they ought to accept the single-hyphen spelling. I've put in a request for that, we'll see what they think ... regards, tom lane -- 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] check for missing tablespaces?
Anyway, from this POV all we really need to know is that the device hosting this tablespace failed to mount when the machine was rebooted, and then postgres restarted. Good to know postgresql had nothing to do with the missing data. I wasn't sure if it was user error, config problem or hardware. From my experience, it doesn't really matter if you have a sophisticated SAN or put together an ad-hoc nas/das solution, storage likes to bite you. The only thing that helps me sleep at night is sound replication and backups (in some cases triple redundancy with aging data sets). Andrew -- 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] search_path improvements
Greg, What's the point of namespaces if not to implement visibility? The interesting thing to do would be to hide all the internal foo functions in a foo.* schema and only put the external api in public. That is an interesting idea. However, what our real users are really doing in the field is more diverse. (see below) That way you can't accidentally call an internal foo function or have a name conflict between two internal functions. The external api could even just be a bunch of thin wrappers around the implementation functions in foo.* (what Oracle calls public synonyms). This assumes that all users should have access to the same public APIs as all other users. Real applications are more complex. In my experience of PostgreSQL applications, people use schema for three different reasons: Organization/Maintainability: when you have hundreds or thousands of database objects, you want folders to put them in just so that you can keep track of them and view them in easy-to-digest groups, just as you deal with files in a filesystem. DBAs no more want to put everything in one big flat namespace, even if the individual names are unique and the permissions are the same, than we want to have all of the PostgreSQL source code in one big directory. Further, these schema names generally indicate something about the purpose of the objects in them: cms,matviews, reports,calendar. When accurate, these schema names aid the DBA in maintaining and troubleshooting the application, and are more convenient than hungarian notation schemes. Visibility: some applications use schema to hide objects from roles which shouldn't see them: inner,cronjobs,acl, whether for data hiding or just to keep private functions and tables separate from what the application accesses directly. However, this approach is not very common *because of* the awkwardness and overhead of search_path; DBAs are constantly troubleshooting search_path omissions and errors and eventually give up on visibility rules, making all schema visible to all users. This gets even more difficult when you consider that in a large complex application with multiple ROLEs, not all ROLEs should see all schema, but what an individual user can access might be a list of schema which represent some-but-not-all schema. The lack of a convenient search_path_add or SET ROLE ... WITH DEFAULTS makes this an unmanageable mess; DBAs find themselves constantly ALTERing each user's search_path individually. Security: schema provide convenient containers to lock up groups of objects by role. admin, permissions etc. schemas combine visibility and USE restrictions to make sql injection much harder, and administrative tasks are supported by objects in schema not accessible to the webuser. As I said before, schema conbine 3 purposes: organization, visibility and security, into one structure. Which is why it's difficult to make them work perfectly for all 3 purposes. We could, however, make them work better. c) the ability as superuser to have my own special schema which are always in the search path, as pg_catalog and $user_temp are.* * if you're not sure why someone would want this, consider information_schema. If your application depends on I_S to work, how do you make sure it's always in every user's search_path? Uhm, wouldn't you just refer to information_schema.foo? What if some other part of your application depends on information_schema *not* being in your path? Using global state for this seems destined to leave you with something broken that can't be fixed without breaking something else. Easily said for someone who doesn't have to adapt a 3rd-party vendor application or support real users on the phone. Insisting that all of your application developers remember to type information_schema. all of the time really makes them love the DBA. Mostly, this simply results in people not using information_schema, and instead using their own home-grown system view scripts, which are often wrong. However, if we had push/pop/shift/unshift for search_path, the need for search_path_suffix would be considerably diminished, since application code ( DBAs) would use push/pop instead of replacing the entire search_path. Hm, I'm beginning to think extensions need to have search_path set on every function or have every object reference everywhere be explicitly pg_extension.* (and/or _private_.* like my earlier suggestion). Again, I'm not talking about Extensions. I think that Extensions are completely orthagonal to search_path, hence the change of subject line. I'm talking about making search_path (and schema) more useful to DBAs and application designers. e) having roles somehow inherit search_path on a SET ROLE*** Grr. I'm still bitter about su doing that on some systems without su -. I think I've lost that battle though and I'm forever doomed to never know what su will do on a new system. As previously
Re: [HACKERS] pg_migrator and an 8.3-compatible tsvector data type
Bruce Momjian wrote: Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I have discovered a simpler solution using ALTER TABLE and calling a conversion function: test= CREATE TABLE tsvector_test(x tsvector); CREATE TABLE test= ALTER TABLE tsvector_test ALTER COLUMN x TYPE tsvector test- USING conversion_func(x); ALTER TABLE No need for a fake data type and the required index infrastructure. I think this is basically a large-caliber foot gun. You're going to pretend that invalid data is valid, until the user gets around to fixing it? What choice do we have? While we can mark indexes as invalid (which we do), how do we mark a table's contents as invalid? Should we create rules so no one can see the data and then have the ALTER TABLE script remove the rules after it is rebuilt? OK, what ideas do people have to prevent access to tsvector columns? I am thinking of renaming the tables or something. Another idea would be to rename just the _column_ (not the table), but that doesn't block SELECT * from showing the column. Another idea would be to replace tsvector's out function, but that would affect all tables meaning it would be hard to incrementally rebuild tables using tsvector columns. Another idea would be to use column-level permissions to prevent column access, but that doesn't affect super-users. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] [GENERAL] INTERVAL SECOND limited to 59 seconds?
Sebastien FLAESCH s...@4js.com writes: I would expect that an INTERVAL SECOND can store more that 59 seconds. I took a look into the SQL spec and I think that we do indeed have a spec compliance issue here. SQL99 section 4.7 saith Within a value of type interval, the first field is constrained only by the interval leading field precision of the associated interval qualifier. Table 8, Valid values for fields in INTERVAL values, specifies the constraints on subsequent field values. [ Table 8 says about what you'd expect, eg 0..23 for HOUR ] Values in interval fields other than SECOND are integers and have precision 2 when not the first field. SECOND, however, can be defined to have an interval fractional seconds precision that indicates the number of decimal digits maintained following the decimal point in the seconds value. When not the first field, SECOND has a precision of 2 places before the decimal point. So in other words, 999 seconds is a valid value for a field of type INTERVAL SECOND, *and should come out the same way*, not as 00:16:39, and certainly not as 00:00:39. It might be a relatively easy fix to not truncate the input value incorrectly. I haven't looked, but I think we should look now, because 8.4 has already changed the behavior in this area and it would be good not to change it twice. The focus of the 8.4 work was to make sure that we would correctly interpret the values of spec-compliant interval literals, but this example shows we are not there yet. We are fairly far away from being able to make it print out as the spec would suggest, because interval_out simply doesn't have access to the information that the field is constrained to be INTERVAL SECOND rather than some other kind of interval. We also have got no concept at all of interval leading field precision, only of interval fractional seconds precision, so constraining the leading field to only a certain number of integral digits isn't possible either. I don't foresee anything getting done about either of those points for 8.4. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Patch: AdjustIntervalForTypmod shouldn't discard high-order data
As I mentioned a bit ago
http://archives.postgresql.org/pgsql-hackers/2009-05/msg01505.php
there seems to be a definite problem still remaining with our handling
of interval literals. To wit, this behavior is absolutely not per spec:
regression=# select '999'::interval second;
interval
--
00:00:39
(1 row)
The correct interpretation of the input value is certainly 999 seconds.
The spec would allow us to throw error if it exceeds the available range
of the field, but a silent modulo operation is not per spec and seems
against our general design principle of not silently discarding data.
I propose the attached patch to make the code not throw away high-order
values in this fashion.
A somewhat more debatable case is this:
regression=# select '1 day 1 hour'::interval hour;
interval
--
01:00:00
(1 row)
which with the attached patch we would render as
regression=# select '1 day 1 hour'::interval hour;
interval
1 day 01:00:00
(1 row)
There is some case to be made that we should throw error here,
which we could do by putting error tests where the attached patch
has comments suggesting an error test. However I'm inclined to think
that doing that would expose an implementation dependency rather more
than we should. It is usually not clear to novices that '1 day 1 hour'
is different from '25 hours', and it would be even less clear why the
latter would be acceptable input for an INTERVAL HOUR field when the
former isn't. So I'm proposing the patch as-is rather than with the
extra error tests, but am open to being convinced otherwise.
The reason I'm bringing this up now is that we've already changed the
behavior of interval literals quite a bit in 8.4. I would rather try to
finish getting it right in this release than have the behavior change
twice in successive releases.
Comments?
regards, tom lane
Index: src/backend/utils/adt/timestamp.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.199
diff -c -r1.199 timestamp.c
*** src/backend/utils/adt/timestamp.c 26 May 2009 02:17:50 - 1.199
--- src/backend/utils/adt/timestamp.c 31 May 2009 22:13:35 -
***
*** 962,967
--- 962,979
int range = INTERVAL_RANGE(typmod);
int precision = INTERVAL_PRECISION(typmod);
+ /*
+* Our interpretation of intervals with a limited set of fields
+* is that fields to the right of the last one specified are
zeroed
+* out, but those to the left of it remain valid. Since we do
not
+* have any equivalent of SQL's interval leading field
precision,
+* we can't properly enforce any limit on the leading field.
(Before
+* PG 8.4 we interpreted a limited set of fields as actually
causing
+* a modulo operation on a given value, potentially losing
high-order
+* as well as low-order information; but there is no support
for such
+* behavior in the standard.)
+*/
+
if (range == INTERVAL_FULL_RANGE)
{
/* Do nothing... */
***
*** 974,1000
}
else if (range == INTERVAL_MASK(MONTH))
{
- interval-month %= MONTHS_PER_YEAR;
interval-day = 0;
interval-time = 0;
}
/* YEAR TO MONTH */
else if (range == (INTERVAL_MASK(YEAR) | INTERVAL_MASK(MONTH)))
{
- /* month is already year to month */
interval-day = 0;
interval-time = 0;
}
else if (range == INTERVAL_MASK(DAY))
{
! interval-month = 0;
interval-time = 0;
}
else if (range == INTERVAL_MASK(HOUR))
{
! interval-month = 0;
! interval-day = 0;
!
#ifdef HAVE_INT64_TIMESTAMP
interval-time = (interval-time / USECS_PER_HOUR) *
USECS_PER_HOUR;
--- 986,1008
}
else if (range == INTERVAL_MASK(MONTH))
{
interval-day = 0;
interval-time = 0;
}
/* YEAR TO MONTH */
else if (range == (INTERVAL_MASK(YEAR) | INTERVAL_MASK(MONTH)))
{
interval-day = 0;
interval-time = 0;
}
else if (range == INTERVAL_MASK(DAY))
{
! /* throw error
Re: [HACKERS] search_path improvements
On Sun, May 31, 2009 at 9:12 PM, Josh Berkus j...@agliodbs.com wrote: This assumes that all users should have access to the same public APIs as all other users. Real applications are more complex. Well the goal is to make them simpler. I don't know any language that has implemented what you describe. Either you have access to the internal methods of a class or you don't and you only have access to the public api. That seems to work for much more sophisticated languages than ours just fine. In my experience of PostgreSQL applications, people use schema for three different reasons: Organization/Maintainability: when you have hundreds or thousands of database objects, you want folders to put them in just so that you can keep track of them and view them in easy-to-digest groups, just as you deal with files in a filesystem. Well when you ls a directory or perform some operation on a file you only work with what's in that directory, not everything in the hierarchy. Visibility: some applications use schema to hide objects from roles which shouldn't see them: inner,cronjobs,acl, whether for data hiding or just to keep private functions and tables separate from what the application accesses directly. However, this approach is not very common *because of* the awkwardness and overhead of search_path; DBAs are constantly troubleshooting search_path omissions and errors and eventually give up on visibility rules, making all schema visible to all users. I don't think that gets any easier with better tools. This is the same reason unix systems don't put every tool in a different directory and then insist you put every directory in your path based on which tools each user should have access to. What you're describing is a fundamentally painful thing to do. You have to decide for every user what objects they should have access to and which they shouldn't. It doesn't get any ideasier if you have every function hard coding inside it assumptions about what schemas it will need. As I said before, schema conbine 3 purposes: organization, visibility and security, into one structure. Which is why it's difficult to make them work perfectly for all 3 purposes. We could, however, make them work better. How is this different from any other analogous system? The filesystem uses directories for all three of the above, for example? Having three different namespaces, one for organizing your code, one to control visibility, and one to control security would be 9 times more complex, i think. Uhm, wouldn't you just refer to information_schema.foo? What if some other part of your application depends on information_schema *not* being in your path? Using global state for this seems destined to leave you with something broken that can't be fixed without breaking something else. Easily said for someone who doesn't have to adapt a 3rd-party vendor application or support real users on the phone. Insisting that all of your application developers remember to type information_schema. all of the time really makes them love the DBA. Mostly, this simply results in people not using information_schema, and instead using their own home-grown system view scripts, which are often wrong. 3rd-party vendor code is precisely what I'm thinking of when I point out that having global state to override what the code requests is a recipe for problems. 3rd-party vendors would be left with no way to write their code such that they could guarantee it would work -- the DBA would always be able to break it by setting this variable. And some other code might require this variable to be set leaving the hapless DBA with no right option. However, if we had push/pop/shift/unshift for search_path, the need for search_path_suffix would be considerably diminished, since application code ( DBAs) would use push/pop instead of replacing the entire search_path. Well I don't mind push but I still think pop is an error. What you really want to do is restore it to the value you started with. You don't want to remove the last element since that may not be the element you added. Some function you called may have added an extra element on the head. -- greg -- 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] search_path improvements
On May 31, 2009, at 3:47 PM, Greg Stark wrote: On Sun, May 31, 2009 at 9:12 PM, Josh Berkus j...@agliodbs.com wrote: This assumes that all users should have access to the same public APIs as all other users. Real applications are more complex. Well the goal is to make them simpler. I don't know any language that has implemented what you describe. Either you have access to the internal methods of a class or you don't and you only have access to the public api. That seems to work for much more sophisticated languages than ours just fine. Right, but PostgreSQL isn't a language, it's a database. And PostgreSQL already has stuff in place to affect visibility of objects. Such is not reasonable for Python, but makes perfect sense in an RDBMS IMHO. Well when you ls a directory or perform some operation on a file you only work with what's in that directory, not everything in the hierarchy. I don't see how this relates to what Josh said. He was just talking about organizing object into schemas, not about trees of schemas AFAICT. I don't think that gets any easier with better tools. This is the same reason unix systems don't put every tool in a different directory and then insist you put every directory in your path based on which tools each user should have access to. But they have tools in a few different directories (/bin, /sbin, /usr/ bin, /usr/sbin, /usr/local/bin, etc.), and it gives you the $PATH environment variable to affect visibility. What you're describing is a fundamentally painful thing to do. You have to decide for every user what objects they should have access to and which they shouldn't. Well, groups of users, yes. Roles. Pretty standard security stuff. It doesn't get any ideasier if you have every function hard coding inside it assumptions about what schemas it will need. It's worth avoiding that, too. Or perhaps objects are aware of their own schemas, just as a subroutine in the Foo::Bar package in Perl can access another subroutine in the same package without having to put Foo::Bar:: in front of it. How is this different from any other analogous system? The filesystem uses directories for all three of the above, for example? Maybe it's not, but it could still be easier to use. Having three different namespaces, one for organizing your code, one to control visibility, and one to control security would be 9 times more complex, i think. But people are doing this already. We should make their jobs easier. 3rd-party vendor code is precisely what I'm thinking of when I point out that having global state to override what the code requests is a recipe for problems. 3rd-party vendors would be left with no way to write their code such that they could guarantee it would work -- the DBA would always be able to break it by setting this variable. And some other code might require this variable to be set leaving the hapless DBA with no right option. But not, perhaps, if objects automatically know about other objects in their own schemas. Put another way, when a function in the foo schema is called, it would transparently use the search path foo, $search_path whenever it tried to do anything. However, if we had push/pop/shift/unshift for search_path, the need for search_path_suffix would be considerably diminished, since application code ( DBAs) would use push/pop instead of replacing the entire search_path. Well I don't mind push but I still think pop is an error. What you really want to do is restore it to the value you started with. You don't want to remove the last element since that may not be the element you added. Some function you called may have added an extra element on the head. I think it's worth it to be complete in the implementation, and not leave things out because we think someone might shoot themselves in the foot. Best, David -- 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] [GENERAL] INTERVAL data type and libpq - what format?
I wrote: In a related example, regression=# select interval '123 11' day; interval -- 134 days (1 row) we seem to be adding the 123 and 11 together. This is, um, surprising behavior ... I'd be inclined to think throwing an error is more appropriate. I looked into this and found out why the code wasn't throwing an error already: it seems to be a case of ancient bit-shaving. DecodeInterval maintains an integer bitmask of field types it's seen already, and reports an error if a new field's bit is already set in that mask. However, DAY and WEEK are coded to share a bit, as are YEAR, DECADE, CENTURY, and MILLENIUM; which means the code can't throw error for multiple occurrences of any one of those field types. However, there is room in the mask for all these fields to have their own bits, so the attached proposed patch just does that. The potential objections I can see to this patch are: (1) it eats more than half of the remaining bits in the DTK_M() mask. This doesn't seem to me to be a big problem --- it's not like people are likely to come up with many more time intervals for us to support. Also we could probably shave some bits by eliminating redundancies if we had to. (For instance, I think IGNORE_DTF could safely be given a code above 31 since there's no need for it to be in the bitmasks.) (2) WEEK, DECADE, CENTURY, and MILLENNIUM are too generic to be used as #define names without some kind of prefix or suffix. This is a real risk, but considering the other #defines in this list have no prefix or suffix, it seems inconsistent to put one on these. The buildfarm should tell us soon enough if there's a real problem (the code does compile cleanly for me). If we do have a problem, we should add a prefix/suffix to all these macros at once; but I'd rather not do that as part of a small bugfix. Comments? regards, tom lane Index: src/backend/utils/adt/datetime.c === RCS file: /cvsroot/pgsql/src/backend/utils/adt/datetime.c,v retrieving revision 1.205 diff -c -r1.205 datetime.c *** src/backend/utils/adt/datetime.c26 May 2009 02:17:50 - 1.205 --- src/backend/utils/adt/datetime.c31 May 2009 23:45:06 - *** *** 3022,3040 tm-tm_hour += val; AdjustFractSeconds(fval, tm, fsec, SECS_PER_HOUR); tmask = DTK_M(HOUR); ! type = DTK_DAY; break; case DTK_DAY: tm-tm_mday += val; AdjustFractSeconds(fval, tm, fsec, SECS_PER_DAY); ! tmask = (fmask DTK_M(DAY)) ? 0 : DTK_M(DAY); break; case DTK_WEEK: tm-tm_mday += val * 7; AdjustFractDays(fval, tm, fsec, 7); ! tmask = (fmask DTK_M(DAY)) ? 0 : DTK_M(DAY); break; case DTK_MONTH: --- 3022,3040 tm-tm_hour += val; AdjustFractSeconds(fval, tm, fsec, SECS_PER_HOUR); tmask = DTK_M(HOUR); ! type = DTK_DAY; /* set for next field */ break; case DTK_DAY: tm-tm_mday += val; AdjustFractSeconds(fval, tm, fsec, SECS_PER_DAY); ! tmask = DTK_M(DAY); break; case DTK_WEEK: tm-tm_mday += val * 7; AdjustFractDays(fval, tm, fsec, 7); ! tmask = DTK_M(WEEK); break; case DTK_MONTH: *** *** 3047,3074 tm-tm_year += val; if (fval != 0) tm-tm_mon += fval * MONTHS_PER_YEAR; ! tmask = (fmask DTK_M(YEAR)) ? 0 : DTK_M(YEAR);
[HACKERS] survey of table blocksize changes
Hi all, I ran a few more tests changing the table blocksizes, similar to: http://archives.postgresql.org/pgsql-hackers/2009-05/msg01068.php I did make one change, specifically enable autovacuum, which I had disabled for the previous thread. The WAL blocksize here is using 16KB. Here's the data: BS notpm % Change from default -- - - 1 3122 -80.1% 2 8719 -44.3% 4 16481 5.3% 8 15659 (default) 16 13896 -11.3% 32 10279 -34.4% Pointers to raw data: BS url -- --- 1 http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.a.wal.16.table1/ 2 http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.a.wal.16.table2/ 4 http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.a.wal.16.table4/ 8 http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.a.16/ 16 http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.a.wal.16.table16/ 32 http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.a.wal.16.table32/ It seems like for DBT2, there might be some benefit to setting the table blocksize to 4KB, but some of this could be noise. But anything smaller than 4GB and larger than 8KB looks like a fairly significant performance drop for DBT2. I wonder if there's any coincidence that the blocksize of the ext2 filesystem is also 4KB. Regards, Mark Wong -- 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] dblink patches for comment
Tom Lane wrote:
It's hard to review it without any docs that say what it's supposed to do.
(And you'd need to patch the docs anyway, eh?)
Here's a much simpler SQL/MED support patch for dblink.
This enforces security in the same manner for FOREIGN SERVER connections
as that worked out over time for other dblink connections. Essentially,
the FOREIGN SERVER and associated user MAPPING provides the needed info
for the libpq connection, but otherwise behavior is the same.
I've also attached a doc patch.
Comments?
Joe
Index: dblink.c
===
RCS file: /opt/src/cvs/pgsql/contrib/dblink/dblink.c,v
retrieving revision 1.77
diff -c -r1.77 dblink.c
*** dblink.c 1 Jan 2009 17:23:31 - 1.77
--- dblink.c 31 May 2009 21:21:16 -
***
*** 46,51
--- 46,52
#include catalog/pg_type.h
#include executor/executor.h
#include executor/spi.h
+ #include foreign/foreign.h
#include lib/stringinfo.h
#include miscadmin.h
#include nodes/execnodes.h
***
*** 96,101
--- 97,103
static void dblink_connstr_check(const char *connstr);
static void dblink_security_check(PGconn *conn, remoteConn *rconn);
static void dblink_res_error(const char *conname, PGresult *res, const char *dblink_context_msg, bool fail);
+ static char *get_connect_string(const char *servername);
/* Global */
static remoteConn *pconn = NULL;
***
*** 165,171
} \
else \
{ \
! connstr = conname_or_str; \
dblink_connstr_check(connstr); \
conn = PQconnectdb(connstr); \
if (PQstatus(conn) == CONNECTION_BAD) \
--- 167,177
} \
else \
{ \
! connstr = get_connect_string(conname_or_str); \
! if (connstr == NULL) \
! { \
! connstr = conname_or_str; \
! } \
dblink_connstr_check(connstr); \
conn = PQconnectdb(connstr); \
if (PQstatus(conn) == CONNECTION_BAD) \
***
*** 210,215
--- 216,222
Datum
dblink_connect(PG_FUNCTION_ARGS)
{
+ char *conname_or_str = NULL;
char *connstr = NULL;
char *connname = NULL;
char *msg;
***
*** 220,235
if (PG_NARGS() == 2)
{
! connstr = text_to_cstring(PG_GETARG_TEXT_PP(1));
connname = text_to_cstring(PG_GETARG_TEXT_PP(0));
}
else if (PG_NARGS() == 1)
! connstr = text_to_cstring(PG_GETARG_TEXT_PP(0));
if (connname)
rconn = (remoteConn *) MemoryContextAlloc(TopMemoryContext,
sizeof(remoteConn));
/* check password in connection string if not superuser */
dblink_connstr_check(connstr);
conn = PQconnectdb(connstr);
--- 227,247
if (PG_NARGS() == 2)
{
! conname_or_str = text_to_cstring(PG_GETARG_TEXT_PP(1));
connname = text_to_cstring(PG_GETARG_TEXT_PP(0));
}
else if (PG_NARGS() == 1)
! conname_or_str = text_to_cstring(PG_GETARG_TEXT_PP(0));
if (connname)
rconn = (remoteConn *) MemoryContextAlloc(TopMemoryContext,
sizeof(remoteConn));
+ /* first check for valid foreign data server */
+ connstr = get_connect_string(conname_or_str);
+ if (connstr == NULL)
+ connstr = conname_or_str;
+
/* check password in connection string if not superuser */
dblink_connstr_check(connstr);
conn = PQconnectdb(connstr);
***
*** 2358,2360
--- 2370,2430
errcontext(Error occurred on dblink connection named \%s\: %s.,
dblink_context_conname, dblink_context_msg)));
}
+
+ /*
+ * Obtain connection string for a foreign server
+ */
+ static char *
+ get_connect_string(const char *servername)
+ {
+ ForeignServer *foreign_server;
+ UserMapping *user_mapping;
+ ListCell *cell;
+ StringInfo buf = makeStringInfo();
+ ForeignDataWrapper *fdw;
+ AclResult aclresult;
+
+ /* first gather the server connstr options */
+ foreign_server = GetForeignServerByName(servername, true);
+
+ if (foreign_server)
+ {
+ Oid serverid = foreign_server-serverid;
+ Oid fdwid = foreign_server-fdwid;
+ Oid userid = GetUserId();
+
+ user_mapping = GetUserMapping(userid, serverid);
+ fdw = GetForeignDataWrapper(fdwid);
+
+ /* Check permissions, user must have usage on the server. */
+ aclresult = pg_foreign_server_aclcheck(serverid, userid, ACL_USAGE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, ACL_KIND_FOREIGN_SERVER, foreign_server-servername);
+
+ foreach (cell, fdw-options)
+ {
+ DefElem *def = lfirst(cell);
+
+ appendStringInfo(buf, %s='%s' , def-defname, strVal(def-arg));
+ }
+
+ foreach (cell, foreign_server-options)
+ {
+ DefElem *def = lfirst(cell);
+
+ appendStringInfo(buf, %s='%s' , def-defname, strVal(def-arg));
+ }
+
+ foreach (cell, user_mapping-options)
+ {
+
+ DefElem *def = lfirst(cell);
+
+ appendStringInfo(buf, %s='%s' , def-defname, strVal(def-arg));
+ }
+
+ return pstrdup(buf-data);
+ }
[HACKERS] PostgreSQL 8.4 beta 2 restore error
I just downloaded PostgreSQL 8.4 beta 2 and tried to restore my old database from version 8.3. I created a new database with the same name as my old one. Then when I tried to restore the old database I got the error posted below. I'm running Windows 7 RC. I realize that probably isn't supported yet, but I thought I'd write anyhow. Everything else seems to be working fine other than one error I got during install. It said the secondary logon service wasn't started. In Windows 7 that isn't on by default. Starting the service allowed installation to continue. Will I have to set that to automatic in order to have Postgre work after if I reboot? Is the error I got during restore something I need to be worried about? Thanks so much. C:\Program Files\PostgreSQL\8.4-beta2\bin\pg_restore.exe --host localhost --port 5432 --username postgres --dbname HoldemManager --verbose E:\back up\Poker\Holdem Manager backup\Holdem Manager database.backup pg_restore: connecting to database for restore pg_restore: creating SCHEMA public pg_restore: creating COMMENT SCHEMA public pg_restore: creating PROCEDURAL LANGUAGE plpgsql pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 422; 2612 16386 PROCEDURAL LANGUAGE plpgsql postgres pg_restore: [archiver (db)] could not execute query: ERROR: language plpgsql already exists Command was: CREATE PROCEDURAL LANGUAGE plpgsql;
[HACKERS] Win32 link() function
pg_migrator needs hard link() capabiity on Win32 to support its --link option. Can someone create that and hopefully add it to libpgport? libpgport currently only has symlink capability for Win32. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] information_schema.columns changes needed for OLEDB
On Sun, May 31, 2009 at 11:41 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'd prefer to avoid a catversion bump at this stage of the cycle, but it looks like any changes here would merely involve the bodies of some functions in information_schema.sql. I think we could just change them without a catversion bump. Any beta testers who actually care could easily insert the new definitions without an initdb. Is this a regression relative to 8.3? If not, why not leave it for 8.5? It seems a little bit late in the game to be messing with this. I admit I don't have any particular reason to think it will break anything, but there are other minor patches floating around that meet that criteria too, so if this one then why not those? ...Robert -- 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] information_schema.columns changes needed for OLEDB
Robert Haas robertmh...@gmail.com writes: Is this a regression relative to 8.3? If not, why not leave it for 8.5? No. it's not a regression. What I was concerned about was mainly that it seemed likely to drop off the radar screen without being addressed at all, since most of the other things Konstantin complained about seemed to be we-won't-change-this items. I have no objection to a conscious decision to leave it alone for 8.4, so long as it gets memorialized as either a pending patch or a TODO item. regards, tom lane -- 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] Win32 link() function
Bruce Momjian br...@momjian.us writes: pg_migrator needs hard link() capabiity on Win32 to support its --link option. Can someone create that and hopefully add it to libpgport? AFAIK hard links simply don't exist on Windows. regards, tom lane -- 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] Win32 link() function
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: pg_migrator needs hard link() capabiity on Win32 to support its --link option. Can someone create that and hopefully add it to libpgport? AFAIK hard links simply don't exist on Windows. Magnus showed me this: http://msdn.microsoft.com/en-us/library/aa363860(VS.85).aspx BOOL WINAPI CreateHardLink( __inLPCTSTR lpFileName, __inLPCTSTR lpExistingFileName, __reserved LPSECURITY_ATTRIBUTES lpSecurityAttributes ); -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Win32 link() function
Bruce Momjian br...@momjian.us wrote: pg_migrator needs hard link() capabiity on Win32 to support its --link option. Can someone create that and hopefully add it to libpgport? libpgport currently only has symlink capability for Win32. Can we use CreateHardLink() ? http://msdn.microsoft.com/en-us/library/aa363860.aspx Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- 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] pg_migrator and an 8.3-compatible tsvector data type
On Sat, May 30, 2009 at 11:58 PM, Bruce Momjian br...@momjian.us wrote: The way the restore works, you would have to call the data type 'tsvector', and then that oid is linked around in all sort of odd places and you need to change them all, hence confusion. In theory, I think it would be possible to hack around this problem by doing the following: 1. Change the OID of 8.4's tsvector type to an OID that was not in use in 8.3. 2. Add a type called tsvector83 to 8.4 using the same OID that the tsvector type used in 8.3. 3. Hack pg_dump so that when dumping in upgrade mode, tsvector references from 8.3 are emitted as tsvector83. This would mean that you'd be on line immediately following the upgrade, because the tsvector83 type would still be usable; it just wouldn't have the improved performance of the new tsvector. Unfortunately, it would also require a catversion bump (and some programming work besides, not sure how much). Given that we are 7 months after the start of the last commitfest, which was theoretically our final feature freeze, this is probably not realistic, but if it's not too ugly it might be considered if a similar situation comes up during 8.5 development. (At the risk of beating a dead horse, note if we were upgrading the catalog tables directly via SQL, this type of scenario could be handled cleanly without hacking pg_dump; I repeat my earlier critique that the pg_migrator approach consigns us to a never-ending series of pg_dump hacks, that or having it not work very well.) ...Robert -- 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] Win32 link() function
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: AFAIK hard links simply don't exist on Windows. Magnus showed me this: http://msdn.microsoft.com/en-us/library/aa363860(VS.85).aspx Hmm, interesting. Are we requiring our DBs to be on NTFS already? What are the implications for Cygwin? regards, tom lane -- 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] Win32 link() function
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: AFAIK hard links simply don't exist on Windows. Magnus showed me this: http://msdn.microsoft.com/en-us/library/aa363860(VS.85).aspx Hmm, interesting. Are we requiring our DBs to be on NTFS already? I think we require NTFS with the installers but you can install the source on anything: http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows#Can_I_install_PostgreSQL_on_a_FAT_partition.3F For this reason, the PostgreSQL installer package will not initialise a database cluster on anything but an NTFS partition. The server and utilities may be installed on any partition type. What are the implications for Cygwin? No idea. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] pg_migrator and an 8.3-compatible tsvector data type
Robert Haas wrote: On Sat, May 30, 2009 at 11:58 PM, Bruce Momjian br...@momjian.us wrote: The way the restore works, you would have to call the data type 'tsvector', and then that oid is linked around in all sort of odd places and you need to change them all, hence confusion. In theory, I think it would be possible to hack around this problem by doing the following: 1. Change the OID of 8.4's tsvector type to an OID that was not in use in 8.3. 2. Add a type called tsvector83 to 8.4 using the same OID that the tsvector type used in 8.3. 3. Hack pg_dump so that when dumping in upgrade mode, tsvector references from 8.3 are emitted as tsvector83. The pg_type oids are never transfered by pg_migrator; all schema information is transfered as pg_dump --schema stuff, i.e. ASCII. This would mean that you'd be on line immediately following the upgrade, because the tsvector83 type would still be usable; it just wouldn't have the improved performance of the new tsvector. Unfortunately, it would also require a catversion bump (and some programming work besides, not sure how much). Given that we are 7 months after the start of the last commitfest, which was theoretically our final feature freeze, this is probably not realistic, but if it's not too ugly it might be considered if a similar situation comes up during 8.5 development. (At the risk of beating a dead horse, note if we were upgrading the catalog tables directly via SQL, this type of scenario could be handled cleanly without hacking pg_dump; I repeat my earlier critique that the pg_migrator approach consigns us to a never-ending series of pg_dump hacks, that or having it not work very well.) We could create infrastructure to handle the 8.3 storage format but my guess is that most people would just rather rebuild the tables. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] pg_migrator and an 8.3-compatible tsvector data type
Robert Haas robertmh...@gmail.com writes: (At the risk of beating a dead horse, note if we were upgrading the catalog tables directly via SQL, this type of scenario could be handled cleanly without hacking pg_dump; I repeat my earlier critique that the pg_migrator approach consigns us to a never-ending series of pg_dump hacks, that or having it not work very well.) Updating the catalog tables directly via SQL? Good luck making that work. If you ever get it to work at all, it'll require a pile of hacks that will make pg_migrator look like paradise. (Which is not to say that pg_migrator isn't a hack; it surely is. But it looks like the least painful approach available.) regards, tom lane -- 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] Win32 link() function
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Hmm, interesting. Are we requiring our DBs to be on NTFS already? Oh, yea, we only support tablespaces on Win32 using NTFS. Well, the important point there is that we fail gracefully if you try to use tablespaces when not on NTFS. So you're going to have to make sure that pg_migrator's --link option fails gracefully when not on NTFS. (Come to think of it, --link can fail on Unix too, if the user tries to put the new database on a different filesystem. Have you got guards in there to make sure this is discovered before the point of no return?) regards, tom lane -- 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] Win32 link() function
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Hmm, interesting. Are we requiring our DBs to be on NTFS already? Oh, yea, we only support tablespaces on Win32 using NTFS. Well, the important point there is that we fail gracefully if you try to use tablespaces when not on NTFS. So you're going to have to make sure that pg_migrator's --link option fails gracefully when not on NTFS. I will just check the return code and exit on error. (Come to think of it, --link can fail on Unix too, if the user tries to put the new database on a different filesystem. Have you got guards in there to make sure this is discovered before the point of no return?) Of course: if ((msg = linkAndUpdateFile(ctx, pageConverter, oldfile, newfile)) != NULL) pg_log(ctx, PG_FATAL, error while creating link from %s.%s(%s) to %s.%s(%s): %s\n, oldnspname, oldrelname, oldfile, newnspname, newrelname, newfile, msg); ... if (pg_link_file(src, dst) == -1) return strerror(errno); though you have to delete the new cluster directory and remove the _old suffixes to get your old cluster back. I don't check before starting the migration. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Win32 link() function
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: (Come to think of it, --link can fail on Unix too, if the user tries to put the new database on a different filesystem. Have you got guards in there to make sure this is discovered before the point of no return?) Of course: ... though you have to delete the new cluster directory and remove the _old suffixes to get your old cluster back. That wasn't what I had in mind by before the point of no return. You should be making some effort to detect obvious failure cases *before* the user has to do a lot of error-prone manual cleanup. regards, tom lane -- 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] Win32 link() function
Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: AFAIK hard links simply don't exist on Windows. Magnus showed me this: http://msdn.microsoft.com/en-us/library/aa363860(VS.85).aspx Hmm, interesting. Are we requiring our DBs to be on NTFS already? I think we require NTFS with the installers but you can install the source on anything: http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows#Can_I_install_PostgreSQL_on_a_FAT_partition.3F For this reason, the PostgreSQL installer package will not initialise a database cluster on anything but an NTFS partition. The server and utilities may be installed on any partition type. Oh, yea, we only support tablespaces on Win32 using NTFS. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Win32 link() function
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: (Come to think of it, --link can fail on Unix too, if the user tries to put the new database on a different filesystem. Have you got guards in there to make sure this is discovered before the point of no return?) Of course: ... though you have to delete the new cluster directory and remove the _old suffixes to get your old cluster back. That wasn't what I had in mind by before the point of no return. You should be making some effort to detect obvious failure cases *before* the user has to do a lot of error-prone manual cleanup. That is something I will address during beta as I get problem reports. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Win32 link() function
Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: (Come to think of it, --link can fail on Unix too, if the user tries to put the new database on a different filesystem. Have you got guards in there to make sure this is discovered before the point of no return?) Of course: ... though you have to delete the new cluster directory and remove the _old suffixes to get your old cluster back. That wasn't what I had in mind by before the point of no return. You should be making some effort to detect obvious failure cases *before* the user has to do a lot of error-prone manual cleanup. That is something I will address during beta as I get problem reports. I have added that as a TODO. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_standby -l might destory the archived file
Hi, pg_standby can use ln command to restore an archived file, which might destroy the archived file as follows. 1) pg_standby creates the symlink to the archived file '102' 2) '102' is applied 3) the next file '103' doesn't exist and the trigger file is created 4) '102' is re-fetched 5) at the end of recovery, the symlink to '102' is rename to '202', but it still points '102' 6) after recovery, '202' is recycled (rename to '208', which still points '102') 7) '208' is written new xlog records over -- the archived file '102' comes down! One simple solution to fix this problem is copying the content of the symlink (ie. the archived file itself) and deleting it instead of renaming it at the end of recovery. Thought? -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
