[GENERAL] Autovacuum Logging Info?
Hi, I've been trying to see whether or not autovacuum is vacuuming all of my tables, and how often (for my peace of mind). I can see that it is running, but I don't know what it's doing. There are a handful of key tables in our database which suffer quite a bit if their not vacuumed regularly (at least once a day). I see that there's been quite a bit of discussion recently about this, and in a previous discussion about this, Bruce Momjian raised the question of what people want to see. I would like to see which tables were autovacuumed, when they were vacuumed, and maybe some of the summary information which is displayed in the last few lines of the current vacuum analyze verbose. This is information I could get currently by running vacuum analyze verbose (summary info from last few lines) and through my cron settings (db, tables, and when vacuum is run). Until this is resolved, I guess that I'll turn up my logging and start to browse log files. My two cents :) Ron ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Is OpenFTS dead?
On Saturday 08 July 2006 22:34, Michael Glaesemann wrote: > On Jul 8, 2006, at 9:57 , [EMAIL PROTECTED] wrote: > > FTS will get more and more important for a DBMS system, i think pgsql > > should also consider improving this, isn't? > > I believe a very common FTS solution used with PostgreSQL is > tsearch2, which is included in contrib/. Have you looked at tsearch2? > Does it meet your needs? OpenFTS sits on top of Tsearch2. All it is is some high level apis (perl/python) to create a search engine using PostgreSQL and Tsearch2. Sincerely, Joshua D. Drake > > Michael Glaesemann > grzm seespotcode net > > > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Version Discrepancy
Douglas McNaught wrote: rstp <[EMAIL PROTECTED]> writes: pg_config is telling us that we are running version 7.3.6-RH, but when we start psql it shows that we are running 8.1.4 (which is the correct version). [EMAIL PROTECTED] bin]$ pg_config --version PostgreSQL 7.3.6-RH [EMAIL PROTECTED] bin]$ which postmaster ~/bin/postmaster [EMAIL PROTECTED] bin]$ psql imp Welcome to psql 8.1.4, the PostgreSQL interactive terminal. I'm guessing that for some reason the pg_config from 7.X is found first in your PATH. What does 'which pg_config' print? That's it. 'which pg_config' returns /usr/bin/pg_config I guess that I ran it too early on a Saturday morning, I must have blindly assumed that my '[EMAIL PROTECTED] bin]$ pg_config --version' command would look at the current directory first by default. Thanks Doug! Ron -Doug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Can Log filename include database name?
Jaime Casanova writes: so you want a different logfile for every database you connect to? An option to specify a log for database. where do you will log database shared operations like autovacuum, role creation, maybe even a database creation, tablespace creation, etc... In a global logfile. Simmilar with what Apache does for virtual domains. You can specify a log for a virtual domain, and you can also specify a default/global log file. Perfects example of where this would be usefull. Say you have several production databases that work great. You start developing a new system with a new database and you want to keep an eye on the performance of the queries for this new system. Right now this would mean turning on loggin for all of the DBs.. even though you only want to keep tabs in a single DB. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Procedural language functions across servers
Alvaro Herrera wrote: > > I'm considering building a .so on a scratch machine and copying it to the > > production server but I'm not confident that I understand every possible > > implication. > > Or maybe you could install the development Perl package, which at least > on some distros I know include the libperl.so file you need. If you > weren't so misterious about what distro you are using maybe you could > even find someone to tell you what is that package called. Slackware 8.1, extensively hacked, kernel and anything that talks to the outside World patched up to date, firewalled to oblivion. In other words if I want something other than what's there already I have to build it myself- there's only the one Perl package, and I checked the build procedure before I raised my head above the parapet. What's more looking at the Perl source (in other words we're getting into Perl territory here, not Slackware) it appears that you can build with or without a .so, but if you have one it's always used and that slows normal Perl operations which is something I can't afford. Granted that this isn't the most recent distro by far, but I've got reasons for sticking with it. Otherwise most desktops here have Debian on them and I agree that having competent binary package management helps, although it's not a universal panacea. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Procedural language functions across servers
Michael Fuhr wrote: > > > The other thing that I'm thinking is that it's quite possible that (as > > hypothetical examples) PL/Perl, PostGIS and PL/R wouldn't be happy on the > > same machine, at which point the only way to merge their functionality in > > complex work would be to use a "farm". > > What sort of "unhapiness" are you thinking is "quite possible"? Well I must confess that I was rather looking at worst cases here- constructive pessimism :-) However I think one scenario would be if I were relying on binary packages and found that while most of the ones I wanted were in (say) Debian/stable but one was only in Debian/testing. I'd be reluctant in this case to "upgrade" a development machine from stable to testing, and would either go for a scratch machine or for an image running under User Mode Linux. Historically my preference has usually been to build from source, but even then there are cases where installing some prerequisite implies unwelcome mutilation of a stable machine- not strictly database-related but having to install gd and then finding that requires an upgrade of TrueType springs to mind. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Can Log filename include database name?
On 7/7/06, Francisco Reyes <[EMAIL PROTECTED]> wrote: I am currently using a log with the file name format: log_filename = 'postgresql-%Y-%m.log' Is there any way to change the filename do start witht he database name? For now just added to add the database name to each line, but it would be usefull to have each DB written to it's own file. Or even better to be able to specify on a per database basis whether to log or not. so you want a different logfile for every database you connect to? where do you will log database shared operations like autovacuum, role creation, maybe even a database creation, tablespace creation, etc... -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Procedural language functions across servers
> With the untrusted version of a language you can do essentially > anything that language supports. For example, with plperlu, you > could use DBI to open a connection to another database (even another > DBMS like Oracle, MySQL, etc.), issue a query, fetch the results, > and do whatever you want with those results. Thanks Michael, interesting thoughts. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Need help with quote escaping in exim for postgresql
Marc Haber wrote: > Please note that exim is so flexible that it is possible to implement > mail spool storage in an SQL database. In this case, we'd write data > which originated in an untrusted source to the database, not knowing > about encoding at all. If you are going to store things in multiple encodings and you don't know (or don't want to waste time figuring out) which encoding each is on, probably you should be using SQL_ASCII. This means Postgres itself will never try to mess with bytes (it will never recode stuff). The downside is that the sort order may be incorrect in some queries, but I'm not sure if this is a great problem for a mail spool. Otherwise you do have to declare the encoding you are going to enter data in (using the client_encoding config option). Not doing it causes all sort of weird problems. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Procedural language functions across servers
Mark Morgan Lloyd wrote: > Thanks for that. One of the reasons that I am contemplating this is that when > I > built the server it wouldn't build PL/Perl since the underlying distro didn't > provide a libperl.so file. Now I could obviously recompile the distro's Perl > sources but that would mean I'd no longer have a common Perl architecture site > wide; I'm considering building a .so on a scratch machine and copying it to > the > production server but I'm not confident that I understand every possible > implication. Or maybe you could install the development Perl package, which at least on some distros I know include the libperl.so file you need. If you weren't so misterious about what distro you are using maybe you could even find someone to tell you what is that package called. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Need help with quote escaping in exim for postgresql
On Sun, Jul 09, 2006 at 06:16:48PM +0200, Marc Haber wrote: > > I'd suggest adding a PQsetClientEncoding(conn, "Latin1") right after > > you establish a connection. I'm not sure if Exim has any kind of > > declaration about what encoding strings have internally. > > No, it does not. That's your fundamental problem. You cannot safely quote strings if you do not know the encoding of the string you're escaping. That's fundamentally what this bug is about. > > SQL_ASCII may also be an option (assign no special meaning to > > characters at all), but I'm less sure of that. Can email address > > contain multibyte characters? I didn't think so... > > E-Mail addreses themselves can't, but the "comment" field of an > address can. But no validation is done on these strings, ofcourse. > > What about the configuration file? > > It probably can as well. Ditto. I'd suggest forcing the client_encoding to SQL_ASCII. That way multibyte characters will simply get dumped into the strings in the backend. But at least there won't be any issues with the server misinterpreting your string. It may be worthwhile allowing the user to set the encoding, but that just opens the issue up again, because then you actually have to validate the strings are the encoding you think they are. > > Please note that exim is so flexible that it is possible to implement > mail spool storage in an SQL database. In this case, we'd write data > which originated in an untrusted source to the database, not knowing > about encoding at all. That will work just fine, as long as the database encoding is SQL_ASCII, in which case there is no problem... What we're talking about here is invalidly coded UTF-8 and things like SJIS. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Need help with quote escaping in exim for postgresql
* Martijn van Oosterhout: > * If application always sends untrusted strings as out-of-line > parameters, instead of embedding them into SQL commands, it is not > vulnerable. This paragraph should explictly mention PQexecParams (which everybody should use anyway). It seems that Exim's architecture prevents the use of PQexecParams, though. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Procedural language functions across servers
On Sun, Jul 09, 2006 at 03:00:08PM +, Mark Morgan Lloyd wrote: > The other thing that I'm thinking is that it's quite possible that (as > hypothetical examples) PL/Perl, PostGIS and PL/R wouldn't be happy on the same > machine, at which point the only way to merge their functionality in complex > work would be to use a "farm". What sort of "unhapiness" are you thinking is "quite possible"? -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Need help with quote escaping in exim for postgresql
Hi, On Fri, Jul 07, 2006 at 05:15:11PM +0200, Martijn van Oosterhout wrote: > On Fri, Jul 07, 2006 at 03:48:00PM +0200, Marc Haber wrote: > > From what I understand, the correct way would be to use > > PQescapeStringConn, but that function needs an established connection, > > and exim performs string escape "early", way before the actual > > connection is established. > > I just downloaded the code and looked. Thank you very much. > The code never looks or checks the encoding of the database. This is > bad from a security point of view because that means you have no idea > how your queries are going to be interpreted. That's the way exim has always done it. > I'd suggest adding a PQsetClientEncoding(conn, "Latin1") right after > you establish a connection. I'm not sure if Exim has any kind of > declaration about what encoding strings have internally. No, it does not. > You could use UTF-8 but then postgres would complain if you pass any > strings that arn't valid UTF-8. They may or may not be desirable. Possible not desireable. > SQL_ASCII may also be an option (assign no special meaning to > characters at all), but I'm less sure of that. Can email address > contain multibyte characters? I didn't think so... E-Mail addreses themselves can't, but the "comment" field of an address can. > What about the configuration file? It probably can as well. Please note that exim is so flexible that it is possible to implement mail spool storage in an SQL database. In this case, we'd write data which originated in an untrusted source to the database, not knowing about encoding at all. I'm going to point Philip to this thread. Greetings Marc -- - Marc Haber | "I don't trust Computers. They | Mailadresse im Header Mannheim, Germany | lose things."Winona Ryder | Fon: *49 621 72739834 Nordisch by Nature | How to make an American Quilt | Fax: *49 621 72739835 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Need help with quote escaping in exim for postgresql
On Fri, Jul 07, 2006 at 04:53:14PM +0200, Martijn van Oosterhout wrote:
> On Fri, Jul 07, 2006 at 03:48:00PM +0200, Marc Haber wrote:
> > I am the maintainer of Debian's packages for exim4, a powerful and
> > versatile Mail Transfer Agent developed in Cambridge and in wide use
> > throughout the Free Software Community (http://www.exim.org/).
> >
> > One of our daemon flavours has PostgreSQL support. Our security guys
> > have found a flaw in exim regarding quote escaping for PostgreSQL. The
> > bug is filed in Debian's BTS as http://bugs.debian.org/369351 and was
> > transferred to exim's Bugzilla installation as
> > http://www.exim.org/bugzilla/show_bug.cgi?id=107.
>
> Whether or not the quick fix works for you depends entirly on the
> encoding used by the client to talk to the database.
I am afraid that we don't have any information about the encoding used
since exim allows arbitrary SQL commands to be given in the run-time
configuration file.
> * If application always sends untrusted strings as out-of-line
> parameters, instead of embedding them into SQL commands, it is not
> vulnerable.
exim's configuration holds SQL string literals like
|server_condition = "${if and { \
| {!eq{$2}{}} \
| {!eq{$3}{}} \
| {crypteq{$3}{${lookup mysql{SELECT password FROM
customers WHERE ( domain_name = \
|'${domain:$2}' \
|AND local_part = '${local_part:$2}') OR
user_id='$2'}{$value}fail}} }} {yes}{no}}"
(which is code helping exim to do SMTP AUTH against a password
database stored in mysql. Using PostgreSQL is very similiar, so you'll
get the idea - taken from
http://www.exim.org/eximwiki/AuthenticatedSmtpUsingMysql).
> * If client_encoding is a single-byte encoding (e.g., one of the
> LATINx family), there is no vulnerability.
>From what I can see, exim configuration does not allow an encoding to
be set fot the communication with pgsql, and the code does not match
on "client_encoding". Am I right to assume that without special
handling, anything passed to it from the configuration is passed on
verbatim to the pgsql server?
> * If application cannot pass invalidly encoded data to the server,
> there is no vulnerability (this probably includes all Java
> applications, for example, because of Java's handling of Unicode
> strings).
I am afraid that it is quite easy to (mis)configure exim so that it is
possible to pass invalidly encoded data.
> The easiest may be to simply always set the client encoding to
> something like UTF-8 and work the escaping rules so they work with
> that.
If that's possible, it might. We'd need to know how our input data is
encoded and recode to UTF-8, right?
Greetings
Marc
--
-
Marc Haber | "I don't trust Computers. They | Mailadresse im Header
Mannheim, Germany | lose things."Winona Ryder | Fon: *49 621 72739834
Nordisch by Nature | How to make an American Quilt | Fax: *49 621 72739835
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Procedural language functions across servers
On Sun, Jul 09, 2006 at 12:40:56PM +, Mark Morgan Lloyd wrote:
> I know that the FAQ says that the only way to implement a query
> across databases is to use dblink,
The FAQ doesn't say dblink is the only way, it says "contrib/dblink
allows cross-database queries using function calls." However, the
paragraph that says "There is no way to query a database other than
the current one" could be misinterpreted to mean dblink is the only
way if you read "current one" as "method mentioned in the current
FAQ item" rather than as "database to which you are currently
connected."
http://www.postgresql.org/docs/faqs.FAQ.html#item4.17
dbi-link is an alternative to dblink that uses Perl/DBI:
http://pgfoundry.org/projects/dbi-link/
> is this the only way available if additional procedural languages
> are installed?
With the untrusted version of a language you can do essentially
anything that language supports. For example, with plperlu, you
could use DBI to open a connection to another database (even another
DBMS like Oracle, MySQL, etc.), issue a query, fetch the results,
and do whatever you want with those results. Example:
CREATE FUNCTION remote_version(text, text, text) RETURNS text AS $$
use DBI;
my ($dsn, $user, $pass) = @_;
my $dbh = DBI->connect($dsn, $user, $pass);
my @row = $dbh->selectrow_array("SELECT version()");
$dbh->disconnect;
return $row[0];
$$ LANGUAGE plperlu VOLATILE;
SELECT remote_version('dbi:mysql:wopr;host=norad', 'falken', 'joshua');
remote_version
5.0.22-log
(1 row)
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Procedural language functions across servers
Merlin Moncure wrote: > > > Similarly, if I have PostGIS or PL/R on the hacker's server, or- heaven > > forfend- both, is the best way to get at the production server still to use > > dblink? > > dblink allows you to send queries from one server to another in a > couple of different ways. What the 'client' server has installed is > irrelevant...the sql is processed by the 'server' server (in your > example, the production server i think). > > now, you could send the data across via a dblink query/view and > pl/perl process it in your developer box. if you have pl/pgsql > installed on the production server, I would suggest using that though > and just invoking a function call across the dblink ;) Thanks for that. One of the reasons that I am contemplating this is that when I built the server it wouldn't build PL/Perl since the underlying distro didn't provide a libperl.so file. Now I could obviously recompile the distro's Perl sources but that would mean I'd no longer have a common Perl architecture site wide; I'm considering building a .so on a scratch machine and copying it to the production server but I'm not confident that I understand every possible implication. The other thing that I'm thinking is that it's quite possible that (as hypothetical examples) PL/Perl, PostGIS and PL/R wouldn't be happy on the same machine, at which point the only way to merge their functionality in complex work would be to use a "farm". I hasten to say that I don't anticipate trying that, at least /this/ year, I'm just trying to think ahead :-) -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Procedural language functions across servers
On 7/9/06, Mark Morgan Lloyd <[EMAIL PROTECTED]> wrote: I know that the FAQ says that the only way to implement a query across databases is to use dblink, is this the only way available if additional procedural languages are installed? For example, assume I have a production server A that does not have PL/Perl installed, and a hacker's server B (let's assume this is a separate machine) complete with every possible bell and whistle. If I want to apply Perl (in a read-only fashion) to extract data from the production tables is the "best" way to have a PL/Perl function including references to dblink functions? Similarly, if I have PostGIS or PL/R on the hacker's server, or- heaven forfend- both, is the best way to get at the production server still to use dblink? dblink allows you to send queries from one server to another in a couple of different ways. What the 'client' server has installed is irrelevant...the sql is processed by the 'server' server (in your example, the production server i think). now, you could send the data across via a dblink query/view and pl/perl process it in your developer box. if you have pl/pgsql installed on the production server, I would suggest using that though and just invoking a function call across the dblink ;) merlin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Procedural language functions across servers
I know that the FAQ says that the only way to implement a query across databases is to use dblink, is this the only way available if additional procedural languages are installed? For example, assume I have a production server A that does not have PL/Perl installed, and a hacker's server B (let's assume this is a separate machine) complete with every possible bell and whistle. If I want to apply Perl (in a read-only fashion) to extract data from the production tables is the "best" way to have a PL/Perl function including references to dblink functions? Similarly, if I have PostGIS or PL/R on the hacker's server, or- heaven forfend- both, is the best way to get at the production server still to use dblink? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Getting Primary Key Value After Insert
On Sat, Jul 08, 2006 at 11:02:26PM -0700, Richard Broersma Jr wrote: > > > > 3. If you call currval() will it return 20? I would think it does. > > > > Yes it does. > > > > > My understanding is that it will provided your are within a transaction. > > > > As long as you're in the same session you're fine. You would have to go out > > of > > your way to break it but if you're using some sort of connection pooling you > > wouldn't want to pull a fresh connection from the pool, for example. > > Just to clarify, currval() is isolated by the session on not necessarily by > a transaction? Yes, currval() returns the value given by the last nextval() on that sequaence in the current session. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Getting Primary Key Value After Insert
Richard Broersma Jr <[EMAIL PROTECTED]> writes: > Just to clarify, currval() is isolated by the session on not > necessarily by a transaction? Yes, this is spelled out quite clearly in the docs if you care to read them. :) -Doug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Version Discrepancy
rstp <[EMAIL PROTECTED]> writes: > pg_config is telling us that we are running version 7.3.6-RH, but when > we start psql it shows that we are running 8.1.4 (which is the correct > version). > [EMAIL PROTECTED] bin]$ pg_config --version > PostgreSQL 7.3.6-RH > [EMAIL PROTECTED] bin]$ which postmaster > ~/bin/postmaster > [EMAIL PROTECTED] bin]$ psql imp > Welcome to psql 8.1.4, the PostgreSQL interactive terminal. I'm guessing that for some reason the pg_config from 7.X is found first in your PATH. What does 'which pg_config' print? -Doug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Mobile servers replication
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Carlos H. Reimer > Sent: 09 July 2006 12:17 > To: pgsql-general@postgresql.org > Subject: [GENERAL] Mobile servers replication > > Hi, > > We´re looking for a replication solution that could address > the following > situation: every morning our sellers connect to the master > server and make a > copy of all tables they will need during the day to their > laptop database. > After a day of work, with a lot of work done at their local > database they > reconnect to the master server and resync their data. > > In my opinion, to address that, this situation must be taken into > consideration when the application and database design are designed. > > Or is there a magic solution, a product, that could do it? > > I would like to hear some other opinions about this issue. Slony's log shippping feature would probably work for your morning download, and if arranged such that data was uploaded to a different Slony cluster (and merged on the server with the other data later) could also handle the upload. For true synchronisation however, you will need to build it in to the application I suspect. It can be done relatively easily by using a row versioning system in which every updated or inserted row gets an ever-increasing version number attached. At sync time, the client inserts or updates rows with a higher version than it has previously seen. Add an extra table to record deleted rows, and use a client identifier as part of the pkey, or have the server assign 'real' ID's during sync to replace temporary ones assigned on the clients. Also consider conflict resolution (ie. If the client and another user both update the same row). You may be able to merge the changes, or let the user choose to overwrite the previous users changes, or let an algorithm decide. I have a couple of Pocket PC apps that sync SQL Server CE databases with PostgreSQL in this way, with tens of mobile users and it works very well. Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Mobile servers replication
Hi, We´re looking for a replication solution that could address the following situation: every morning our sellers connect to the master server and make a copy of all tables they will need during the day to their laptop database. After a day of work, with a lot of work done at their local database they reconnect to the master server and resync their data. In my opinion, to address that, this situation must be taken into consideration when the application and database design are designed. Or is there a magic solution, a product, that could do it? I would like to hear some other opinions about this issue. Reimer ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Version/Change Management of functions?
# [EMAIL PROTECTED] / 2006-07-07 14:08:08 -0600: > --On July 7, 2006 12:35:53 PM + Roman Neuhauser <[EMAIL PROTECTED]> > wrote: > > ># [EMAIL PROTECTED] / 2006-07-06 22:41:27 -0600: > >>OK I know this is an odd question but I'm working on an app that will > >>rely more and more on database driven functions, and while the app's > >>source is in SVN, and I intend for the source of the SQL scripts to > >>also be there, I was wondering...what are people doing for version > >>control and change management on their custom (esp PL/pgSQL and say > >>PL/Perl) functions? > > > >Well, "people" use a version control system. > > > >Do you have any specific questions? > > Yes, how exactly do you use it. Since there's no way to directly control > whats in the DB via a VCS, further, how do you verify that what is in the > DB is also in the VCS, etc? Simply: there's no "the DB". Developers have no access to the customer installation(s), and putting things into the VCS is the only way for them to produce code, which includes DDL. What's not in the VCS (or generaged during the release process from tools that are tracked) cannot be installed from the tarball. Whether you produce releases for general consumption like the PostgreSQL project does or your installed base consists of a single web server, whether you produce tarballs or run "svn up" on the single Apache box: that's an irrelevant detail of the release process. Each release should contain DDL/DML needed to upgrade the database from the previous release. The developer who wants to change the schema must provide the ALTER statements. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
