[GENERAL] Wordpress-Mu with postgresql
Does any one use Wordpress-Mu with Postgresql? If yes, please tell me the way.
Re: [GENERAL] How to dump JUST procedures/funnctions?
Is pg_get_functiondef an 8.4 appears to be an 8.4 function?
I don't see it in the 8.3 documentation and the servers in question are all
8.3.
Any alternatives for 8.3? pg_proc has the code body, but not the function
declaration, etc.
"Andreas Kretschmer" wrote in message
news:20100320081646.ga26...@tux...
Carlo Stonebanks wrote:
I'd like to dump to text the full SQL required to create/replace all
user-defined functions within a specific schema - but JUST the function
declarations.
We are doing server migration and there are some network paths in the
code I would like to search and replace.
All functions are stored in pg_catalog.pg_proc, you can search the
column prosrc for your network paths.
And you can get the whole function-definition with pg_get_functiondef.
Okay. Now you can run this select:
select 'select pg_get_functiondef (' || oid || ');' from pg_proc where
prosrc ~ 'network path';
The result can you use to run as commands to retrieve all
function-definitions.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] like operation in tsearch
We introduced prefix support in 8.4, so one may use:
=# select to_tsvector('Rumman went to iftekhar to solve it') @@
to_tsquery('ifte:*') as c;
c
---
t
(1 row)
On Sun, 21 Mar 2010, AI Rumman wrote:
I am using Postgresql 8.1 tsearch2.
I need to match a like operation in tsearch. Such as,
Sample data:
Document
..
Rumman went to iftekhar to solve it.
select ...
from ...
where document like '%ifte%'
need to be written in tsearch. Here "ifte" is a name
But in tsearch when I am using to_tsvector, it gives as follows-
'iftekhar':4 'rumman':1 'solv':6 'went':2
postgres=# select to_tsvector('Rumman went to iftekhar to solve it') @@
to_tsquery('ifte') as c;
c
---
f
(1 row)
I am confused how to execute the above like operation in tsearch.
Any help please.
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] like operation in tsearch
I am using Postgresql 8.1 tsearch2.
I need to match a like operation in tsearch. Such as,
Sample data:
Document
..
Rumman went to iftekhar to solve it.
select ...
from ...
where document like '%ifte%'
need to be written in tsearch. Here "ifte" is a name
But in tsearch when I am using to_tsvector, it gives as follows-
'iftekhar':4 'rumman':1 'solv':6 'went':2
postgres=# select to_tsvector('Rumman went to iftekhar to solve it') @@
to_tsquery('ifte') as c;
c
---
f
(1 row)
I am confused how to execute the above like operation in tsearch.
Any help please.
Re: [GENERAL] Restrict allowed database names?
On Mar 20, 2010, at 2:24 PM, Adam Seering wrote: > Hi, > I'm trying to set up an internal general-purpose PostgreSQL server > installation. I want most users with login access to the server to be able > to create databases, but only with names that follow a specified naming > convention (in particular, approximately "is prefixed with the owner's > username"). A subset of administrative users can create users with any name. > The goal is to let users create arbitrary databases, but to force them to > get approval for names that someone else (or some other service) might > conceivably want. > > Is there any way to enforce this within PostgreSQL? Maybe something > like a trigger on CREATE DATABASE, if that's possible? I don't think so. There are several other ways you could do it, though. Put a wrapper script around createdb that "refuses" to create a database named outside of your naming strategy and trust your users not to work around it. The same, but add a cron job that'll drop any badly named database every hour or so. Don't grant any normal database users createdb privs at all, instead requiring them to use an external tool to create databases. Have that tool - whether it be a cgi script or something suid, or some other hack - use a privileged user to create the database. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restrict allowed database names?
On Sat, Mar 20, 2010 at 5:24 PM, Adam Seering wrote: > Hi, >I'm trying to set up an internal general-purpose PostgreSQL server > installation. I want most users with login access to the server to be able > to create databases, but only with names that follow a specified naming > convention (in particular, approximately "is prefixed with the owner's > username"). A subset of administrative users can create users with any > name. The goal is to let users create arbitrary databases, but to force > them to get approval for names that someone else (or some other service) > might conceivably want. > >Is there any way to enforce this within PostgreSQL? Maybe something > like a trigger on CREATE DATABASE, if that's possible? > Hmmm... nothing like that I'm afraid... But, you could possibly make a shell script to the 'createdb' executable that would force a name-style, but even then, for any user to be able to successfully run the command, they need database logon / create database privs, so if someone : cat `which createdb` and you had made a script, they'd see what you were up to. It may be a way to get started though. --Scott M
[GENERAL] Restrict allowed database names?
Hi, I'm trying to set up an internal general-purpose PostgreSQL server installation. I want most users with login access to the server to be able to create databases, but only with names that follow a specified naming convention (in particular, approximately "is prefixed with the owner's username"). A subset of administrative users can create users with any name. The goal is to let users create arbitrary databases, but to force them to get approval for names that someone else (or some other service) might conceivably want. Is there any way to enforce this within PostgreSQL? Maybe something like a trigger on CREATE DATABASE, if that's possible? Thanks, Adam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help me with this tricky join
Thanks! But, since the master can contain many users (user2, user3, and so on) I suppose this won't be a proper solution? Sorry if I was a bit unclear in my description. I.e., the master is of the form: user_id date User1 20010101 User1 2101 User1 19990101 User1 19970101 User2 ... ... Btw, I'm using Postgre version 8.2 and I cannot use subqueries do the GP software. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stopping processes, preventing connections
On Sat, Mar 20, 2010 at 3:57 PM, Herouth Maoz wrote: > > > The problem is not so much danger in upgrading, but the fact that doing so > without using the system's usual security/bugfix update path means > non-standard work for the sysadmin, meaning he has to upgrade every package > on the system using a different upgrade method, being notified about it from > a different source, and needing to check each one in different conditions, > which makes his work impossible. So the policy so far has been "Use the > packages available through debian". So I'll need to check if there is an > upgrade available through that path - and the question is whether it's > worthwhile (i.e. whether the bug in question has indeed been fixed). I'm certain debian keeps the pgsql packages up to date within a few days or at most weeks of their release . -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Restrict allowed database names?
Hi, I'm trying to set up an internal general-purpose PostgreSQL server installation. I want most users with login access to the server to be able to create databases, but only with names that follow a specified naming convention (in particular, approximately "is prefixed with the owner's username"). A subset of administrative users can create users with any name. The goal is to let users create arbitrary databases, but to force them to get approval for names that someone else (or some other service) might conceivably want. Is there any way to enforce this within PostgreSQL? Maybe something like a trigger on CREATE DATABASE, if that's possible? Thanks, Adam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stopping processes, preventing connections
? Scott Marlowe: On Sat, Mar 20, 2010 at 11:44 AM, Herouth Maoz wrote: The server version is 8.3.1. Migration to a higher version might be difficult as far as policies go, if there isn't a supported debian package for it, but if you can point out a version where this has been fixed I might be able to persuade my boss and sysadmin. Most of the time it is more dangerous to NOT update PostgreSQL to the latest minor point version than to stay on an older minor point version. The occasions when a minor point upgrade come out that is dangerous are rare, and the next minor point version to fix it shows up the next day while the broken one is pulled. I think that's happened 1 or 2 times during the time I've been using postgresql. So, if it's 48 hours old and no alarm bells have gone off that it's being pulled and replaced, a pg update is the right thing to do. Backup beforehand, etc. The danger of a change making your application stop are very low, while the danger of leaving some unpatched bit of nastiness in the backend is much greater a possible problem. I.e. data loss / corruption, things like that. And something as mature as 8.3 is now shouldn't be running in production missing two years of patches. Start with the release notes for 8.3.2 and move forward and see if anything there looks like a problem for your app. Behaviour changing changes rarely get into production releases, they get saved for the next major version. If they do they are well noted in the release notes. The problem is not so much danger in upgrading, but the fact that doing so without using the system's usual security/bugfix update path means non-standard work for the sysadmin, meaning he has to upgrade every package on the system using a different upgrade method, being notified about it from a different source, and needing to check each one in different conditions, which makes his work impossible. So the policy so far has been "Use the packages available through debian". So I'll need to check if there is an upgrade available through that path - and the question is whether it's worthwhile (i.e. whether the bug in question has indeed been fixed). Herouth
Re: [GENERAL] stopping processes, preventing connections
On Sat, Mar 20, 2010 at 11:44 AM, Herouth Maoz wrote: > The server version is 8.3.1. Migration to a higher version might be > difficult as far as policies go, if there isn't a supported debian package > for it, but if you can point out a version where this has been fixed I might > be able to persuade my boss and sysadmin. Most of the time it is more dangerous to NOT update PostgreSQL to the latest minor point version than to stay on an older minor point version. The occasions when a minor point upgrade come out that is dangerous are rare, and the next minor point version to fix it shows up the next day while the broken one is pulled. I think that's happened 1 or 2 times during the time I've been using postgresql. So, if it's 48 hours old and no alarm bells have gone off that it's being pulled and replaced, a pg update is the right thing to do. Backup beforehand, etc. The danger of a change making your application stop are very low, while the danger of leaving some unpatched bit of nastiness in the backend is much greater a possible problem. I.e. data loss / corruption, things like that. And something as mature as 8.3 is now shouldn't be running in production missing two years of patches. Start with the release notes for 8.3.2 and move forward and see if anything there looks like a problem for your app. Behaviour changing changes rarely get into production releases, they get saved for the next major version. If they do they are well noted in the release notes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stopping processes, preventing connections
quoth Greg Smith: Herouth Maoz wrote: Aren't socket writes supposed to have time outs of some sort? Stupid policies notwithstanding, processes on the client side can disappear for any number of reasons - bugs, power failures, whatever - and this is not something that is supposed to cause a backend to hang, I would assume. As a general commentary on this area, in most cases where I've seen an unkillable backend, which usually becomes noticed when the server won't shutdown, have resulted from bad socket behavior. It's really a tricky area to get right, and presuming the database backends will be robust in the case of every possible weird OS behavior is hard to guarantee. However, if you can repeatably get the server into this bad state at will, it may be worth spending some more time digging into this in hopes there is something valuable to learn about your situation that can improve the keepalive handling on the server side. Did you mention your PostgreSQL server version and platform? I didn't see the exact code path you're stuck in during a quick look at the code involved (using a snapshot of recent development), which makes me wonder if this isn't already a resolved problem in a newer version. The server version is 8.3.1. Migration to a higher version might be difficult as far as policies go, if there isn't a supported debian package for it, but if you can point out a version where this has been fixed I might be able to persuade my boss and sysadmin. Thank you for referring me to that entry in the FAQ. By the way, the situation repeated itself today as well. Thanks, Herouth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Transaction table
On Sat, Mar 20, 2010 at 5:26 AM, Scott Marlowe wrote: > On Sat, Mar 20, 2010 at 2:47 AM, Deepa Thulasidasan > wrote: > > Dear All, > > > > I have a query in postgresql if any one can support. > > > > A transaction table in a vehicle tracking application is inserted with > the current position of each vehicle at regular interval (seconds). > > This transaction table consists of 12 columns, which are of the type > varchar, time, numeric or double precision. A new transaction table is > created every day. Total number of records at the end of the day is around 1 > million records. Data is only inserted in to this table and there is no > update or delete. This table is indexed using 2 columns. Now, we are > expecting this transaction table to grow by 10 times in near future. In this > regard, we would like to know if this same structure of the transaction > table and the indexing would be sufficient for quick retrivel of data or do > we have to partition this table? If so what kind of partition would be > suitable? > > You generally wanna partition on the one (or maybe two) fields you'll > regularly use to restrict your data set. For many systems like this > that is a partition on date. Sometimes you can partition on two > things, and if it makes sense to do so your current usage patterns > will show it. Normally one axis of partitioning is fine. > That'll help with reporting, how are you inserting the data now? Are you using individual inserts, or are you loading in batches. Typically, if you can buffer some of the inserts into a group on the application side and then load them you'll be better off then just doing straight inserts for every 'event'. Then, you can combine that with partitioning to report over multiple days pretty easily. --Scott Mead (Just realized that not only are there lots of Scott's on this list... we have multiple Scott M's! :) > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] Transaction table
On Sat, Mar 20, 2010 at 2:47 AM, Deepa Thulasidasan wrote: > Dear All, > > I have a query in postgresql if any one can support. > > A transaction table in a vehicle tracking application is inserted with the > current position of each vehicle at regular interval (seconds). > This transaction table consists of 12 columns, which are of the type > varchar, time, numeric or double precision. A new transaction table is > created every day. Total number of records at the end of the day is around 1 > million records. Data is only inserted in to this table and there is no > update or delete. This table is indexed using 2 columns. Now, we are > expecting this transaction table to grow by 10 times in near future. In this > regard, we would like to know if this same structure of the transaction table > and the indexing would be sufficient for quick retrivel of data or do we > have to partition this table? If so what kind of partition would be suitable? You generally wanna partition on the one (or maybe two) fields you'll regularly use to restrict your data set. For many systems like this that is a partition on date. Sometimes you can partition on two things, and if it makes sense to do so your current usage patterns will show it. Normally one axis of partitioning is fine. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Transaction table
Dear All, I have a query in postgresql if any one can support. A transaction table in a vehicle tracking application is inserted with the current position of each vehicle at regular interval (seconds). This transaction table consists of 12 columns, which are of the type varchar, time, numeric or double precision. A new transaction table is created every day. Total number of records at the end of the day is around 1 million records. Data is only inserted in to this table and there is no update or delete. This table is indexed using 2 columns. Now, we are expecting this transaction table to grow by 10 times in near future. In this regard, we would like to know if this same structure of the transaction table and the indexing would be sufficient for quick retrivel of data or do we have to partition this table? If so what kind of partition would be suitable? Awaiting positive reply. Regards, Deepa. The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to dump JUST procedures/funnctions?
Carlo Stonebanks wrote:
> I'd like to dump to text the full SQL required to create/replace all
> user-defined functions within a specific schema - but JUST the function
> declarations.
>
> We are doing server migration and there are some network paths in the
> code I would like to search and replace.
All functions are stored in pg_catalog.pg_proc, you can search the
column prosrc for your network paths.
And you can get the whole function-definition with pg_get_functiondef.
Okay. Now you can run this select:
select 'select pg_get_functiondef (' || oid || ');' from pg_proc where prosrc ~
'network path';
The result can you use to run as commands to retrieve all
function-definitions.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to dump JUST procedures/funnctions?
I'd like to dump to text the full SQL required to create/replace all user-defined functions within a specific schema - but JUST the function declarations. We are doing server migration and there are some network paths in the code I would like to search and replace. Carlo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help me with this tricky join
Jay wrote: > Hi, > > I'm somewhat new to SQL so I need a bit of help with this problem. So > I have 2 tables: "selection" and "master", both have two columns in > each: "user_id" and "date". > > The "selection" contains one row for each "user_id" and depicts _one_ > "date" value for each user. > The "master" contains all "date" changes for each "user_id". I.e., > there are many dates for each "user_id". It is a history of previous > occurrences. > > Now, I want to add a 3rd column to the "selection" table that is the > "date" value from one step back for each "user_id". I.e., if the > "master" contains: > > User1 20010101 > User1 2101 > User1 19990101 > User1 19970101 > > for User1, and the "selection" is > > User1 19990101 > > I want this to become: > > User1 2101 19990101 > > How do I do this? A simple join wont do it since it is dependent on > what value "date" is for each user.. I think, you don't need a new column, because you can determine this value (assuming you have 8.4) test=*# select * from selection ; user_id | date -+-- user1 | 20010101 user1 | 2101 user1 | 19990101 user1 | 19970101 (4 Zeilen) Zeit: 0,255 ms test=*# select *, lag(date) over (order by date)from selection order by date desc; user_id | date | lag -+--+-- user1 | 20010101 | 2101 user1 | 2101 | 19990101 user1 | 19990101 | 19970101 user1 | 19970101 | (4 Zeilen) Regards, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
