Re: [GENERAL] pg_dump order of rows
Can you please explain why? ( just curious, this seems a very strange requirement ) I'm on some research for election systems - an interesting field. On one hand there is a requirement of realiablility, so there is the need for stable and proven software and backups of systems for example. On the other hand transparency is also very important, so backing up using a simple pg_dump is more comprehensible than using a dedicated tool to users and administrators. Of course there is no unencrypted data in the database - but maintaining the order of insertions in a dumpfile to a specific table could theoretically leak some kind of information, hence the question. I'm not aware of any database where I can influence the on-disk order directly, but there may be some hints I'm probably missing (even a vacuum full will only compact the datafile as far as I know but not reoder it accoring to an index or something). Jan -- 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] Performance tuning in Pgsql
On Sun, Dec 12, 2010 at 9:57 PM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Sorry Sir, but I simply followed your steps. I think those are sufficient. But my server didn't start after these changes. Here are my steps : cd /hrd2-p/postgres_data /etc/init.d/postgresql-8.4 stop mkdir -p /opt/pg_xlog chown -R postgres.postgres /opt/pg_xlog chmod 700 /opt/pg_xlog cp -rf pg_xlog/* /opt/pg_xlog mv pg_xlog pg_xlog_old ln -s /opt/pg_xlog pg_xlog /etc/init.d/postgresql-8.4 start Starting PostgreSQL 8.4: waiting for server to start...could not start server PostgreSQL 8.4 did not start in a timely fashion, please see /hrd2-p/postgres_data/pg_log/startup.log for details uima-server:/hrd2-p/postgres_data # vim /hrd2-p/postgres_data/pg_log/startup.log At this time this log is empty. Also I didn/t make any changes in postgresql.conf I'm using Linux ( Linux uima-server 2.6.16.46-0.12-smp #1 SMP Thu May 17 14:00:09 UTC 2007 x86_64 x86_64 x86_64 GNU/Linux ) and postgres ( Postgres 8.4 ) Without that log file it's pretty much impossible to guess what went wrong. Does your system have SELinux installed? Can it be disabled for testing? Are the files in /hrd2-p/postgres_data owned by the postgres user or someone else? -- 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] pg_dump order of rows
On 2010-12-13 09:12, Jan Kesten wrote: I'm not aware of any database where I can influence the on-disk order directly, but there may be some hints I'm probably missing (even a vacuum full will only compact the datafile as far as I know but not reoder it accoring to an index or something). CLUSTER should be able to do that for you. See http://www.postgresql.org/docs/9.0/static/sql-cluster.html. / Hampus -- 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] Urgent -- High memory usage on PostgreSQL server
On Sun, Dec 12, 2010 at 09:49:52PM -0800, savio rodriges wrote: Hello, We are facing very HIGH memory utilization on postgreSQL server and need help. which number from all of what's below is making you worried? Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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] Do we want SYNONYMS?
On 2010-12-07, Andy Colson a...@squeakycode.net wrote: I think it covers parts. In both you can create an alias to a table, both of which you can fire off insert/update/delete. I assume in PG you could have different permissions for the table and the alias, which I assume you can do in oracle. If we pretend oracle and PG both have the same thing as a schema, and using PG's definition of schema: I assume in oracle you can create table synonym schemaA.bob for schemaB.tablex And I assume you could do the same in PG. However beyond that, I dont know what oracle supports that we'd need. They want synonyms for functions, but as far as I can see the same can be achieved with minimal extra work by creating a new LANGUAGE SQL function that calls the original. CREATE FUNCTION newschema.newname( atype ... ) RETURNS rtype AS ' select oldschema.oldname ( $1 ... ) ' LANGUAGE SQL; with apropriare values for the lowercase bits and elipsis. -- ⚂⚃ 100% natural -- 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] encode(bytea_value, 'escape') in PostgreSQL 9.0
On 2010-12-06, Florian Weimer fwei...@bfk.de wrote: * Tom Lane: Florian Weimer fwei...@bfk.de writes: The old 'escape' encoding used by PostgreSQL 8.4 and prior was pretty helpful for getting human-readable strings in psql. It seems this functionality was removed in PostgreSQL 9.0. Was this an accident or a deliberate decision? Could we get it back, please? I think you're looking for set bytea_output = escape. To me, this seems problematic as a general recommendation because programs won't use this, programs should be using libpq's unescape_bytea and thus be immune to changes in the representation. If you want predictable representation base64 (or hex) your data and store it in a text column postgres will compress long values so not hardly any space will be wasted. and it's confusing to have different output in psql than what your program sees. That's why I don't want to put it into .psqlrc. The separate command will raise a few eyebrows here and there. 8-/ ALTER DATABASE whatever SET bytea_output = escape; or ALTER USER whoever SET bytea_output = escape; -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fatal accident :)
2010-12-13 11:19:35 CET FATAL: the database system is starting up pasman -- 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] Fatal accident :)
2010/12/13 pasman pasmański pasma...@gmail.com 2010-12-13 11:19:35 CET FATAL: the database system is starting up And the question is??? btw, this is quite normal behaviour when postgres is starting and someone wants to connect. regards Szymon Guz
[GENERAL] Problem while pg_xlog directory
Dear all, I am doing some Performance tuning in Pgsql. I have changed several Parameters in postgresql.conf file. Now I want to change my pg_xlog directory to another disk. I followed the below steps : cd /hrd2-p/postgres_data /etc/init.d/postgresql-8.4 stop mkdir -p /opt/pg_xlog chown -R postgres.postgres /opt/pg_xlog chmod 700 /opt/pg_xlog cp -rf pg_xlog/* /opt/pg_xlog mv pg_xlog pg_xlog_old ln -s /opt/pg_xlog pg_xlog /etc/init.d/postgresql-8.4 start But me server fail to start and there is empty log in startup.log. I also checked my permissions and directories are owned by postgres user. Is I am failing something and there is some simple way. Also, i want to know after Performance tuning following the below link : http://wiki.postgresql.org/wiki/Performance_Optimization. How much our performance increases. I am selecting data from 5Gb file and it takes 229477 ms. Still i execute same query in 226977 ms after some tuning. Please help. Best Regards Adarsh Sharma -- 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] Using regexp_replace to remove small words
On Fri, Dec 10, 2010 at 5:59 PM, Peter Eisentraut pete...@gmx.net wrote: select regexp_replace('Tommy Lee Jones', $$\y\w{2,3}\y$$, ' ', 'g' ); speaking of regular expressions... the other day I was trying to find where the 'flags' option to regexp_replace() is described, but I cannot find it in the 9.0 manual in any obvious place. it is not described in the string functions section, nor in the regexp section. -- 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] pg_dump order of rows
On Mon, Dec 13, 2010 at 4:26 AM, Hampus Wessman hampus.wess...@gmail.com wrote: CLUSTER should be able to do that for you. See http://www.postgresql.org/docs/9.0/static/sql-cluster.html. That's the exact opposite of what he wished to accomplish. The only thing I can suggest is to do random order of updates, perhaps while also setting the fill factor for the pages to something large so there's no room to re-use a page, causing more shuffling. I suspect you'll have to do this for every table you've got, else you could leak some information about what rows were updated since the last import if the person has access to the file used to import the data originally. You'll also have to do this every time you dump the DB, I'd suspect... -- 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] Problem while pg_xlog directory
On Mon, Dec 13, 2010 at 8:47 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: But me server fail to start and there is empty log in startup.log. I also checked my permissions and directories are owned by postgres user. So there are absolutely no warnings emitted to the screen or anywhere else? Seems very very unlikely to me. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Multilingual full text indexing
I have a table ( named test) with following strucuture ID, lang, Name, Name_en, Name_de http://postgresql.1045698.n5.nabble.com/file/n3303131/Capture.jpg I want to do a full text seach on the table. I can create a ts_vector and store in another column by gin_tsvector = tsvector('de', Titel_de) || tsvector('en' Titel_en) . But my problem is that when i want to select and display the column Name_en if the text is being entered in english language. How can i select column based on the text being entered? select id,(???) from test where gin_tsvector @@ to_tsquery ('english', 'Ivalo Airport') For example, if user entered Ivalo Airport, i want to select column Name_en and if user entered Flughafen Ivalo, i want to select column Name_de. Any help would be highly appreciated ! -- View this message in context: http://postgresql.1045698.n5.nabble.com/Multilingual-full-text-indexing-tp3303131p3303131.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
RES: [GENERAL] Using regexp_replace to remove small words
Hi Peter, Your example works perfectly. But, I need Your help with on another situation. We're trying to create a plpgsql function with the expression. But, I'm getting a syntax error: - create or replace function sp_remove_small_words( ptext text ) returns text immutable as $$ begin return regexp_replace( ptext, $$\y\w{1,3}\y$$, '', 'g' ); end; $$ language plpgsql ; - ERRO: erro de sintaxe em ou próximo a \ LINE 6: return regexp_replace( ptext, $$\y\w{1,3}\y$$, '', 'g' ); ^ ** Error ** ERRO: erro de sintaxe em ou próximo a \ SQL state: 42601 Character: 138 Thanks again! -Mensagem original- De: Peter Eisentraut [mailto:pete...@gmx.net] Enviada em: sexta-feira, 10 de dezembro de 2010 20:59 Para: Henrique de Lima Trindade Cc: pgsql-general@postgresql.org Assunto: Re: [GENERAL] Using regexp_replace to remove small words On fre, 2010-12-10 at 10:47 -0200, Henrique de Lima Trindade wrote: I'm trying to find a regular expression that removes all small (length N) words from a string. But, until now I've not been successful. Here is a start: select regexp_replace('Tommy Lee Jones', $$\y\w{2,3}\y$$, ' ', 'g' ); If you want to normalize the spaces after the removal and handle beginning and end of the word, you will need to expand this to cover those cases, but the example should contain the key ingredients. -- 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] Using regexp_replace to remove small words
On Mon, Dec 13, 2010 at 7:24 AM, Henrique de Lima Trindade henri...@vivver.com.br wrote: Your example works perfectly. But, I need Your help with on another situation. We're trying to create a plpgsql function with the expression. But, I'm getting a syntax error: You're using $$ quoting for the function, and $$ quoting for the string in the regexp. Think of it like you're trying to embed a ' in a string delimited by ' also. -- 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] pg_dump order of rows
Not at all. He wanted a way to influence the on-disk order of the rows in a table, so he could make sure they were not left in the order of insertion. That should be possible with CLUSTER. From the documentation: When a table is clustered, it is physically reordered based on the index information (again, see http://www.postgresql.org/docs/9.0/static/sql-cluster.html for full details). Now, if one would give CLUSTER an index that put the data back in insertion order, then it wouldn't be very useful (obviously). On the other hand, if there is such an index, then the insertion order of the rows can easily be restored no matter how they are reordered... (making the whole thing pointless in this case). So, what index to choose? He can either cluster on an existing index, if that would remove all trace of the insertion order (any field with unique values would work equally well), or he can add a field with random values, index that field and cluster on that index. CLUSTER is just the tool... Unless I've missed something fundamental about the CLUSTER command it should be perfectly possible to reorder the data on disk in any way you want with it, if you first create an appropriate index to cluster on. The downsides I see is (1 2 directly from the documentation): 1. When a table is being clustered, an ACCESS EXCLUSIVE lock is acquired on it. 2. You need free space on disk at least equal to the sum of the table size and the index sizes. 3. Rows inserted after CLUSTER finishes will not be protected. You would need to run it before a dump and make sure nothing is written to the table between the clustering and the dumping, if you want to be really sure... Any reason why this wouldn't work? On 2010-12-13 14:53, Vick Khera wrote: On Mon, Dec 13, 2010 at 4:26 AM, Hampus Wessman hampus.wess...@gmail.com wrote: CLUSTER should be able to do that for you. See http://www.postgresql.org/docs/9.0/static/sql-cluster.html. That's the exact opposite of what he wished to accomplish. The only thing I can suggest is to do random order of updates, perhaps while also setting the fill factor for the pages to something large so there's no room to re-use a page, causing more shuffling. I suspect you'll have to do this for every table you've got, else you could leak some information about what rows were updated since the last import if the person has access to the file used to import the data originally. You'll also have to do this every time you dump the DB, I'd suspect... -- 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] pg_dump order of rows
I'm not aware of any database where I can influence the on-disk order directly Hello, You may want to export your main tables using COPY instead of pg_dump. Doing this, you could disorder your tables, e.g. with hashtext: COPY (select * from foo order by hashtext(some_col) )TO file Getting a consistent dump of all your data may be an issue thought... regards, Marc Mamin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Cluster with LATIN1 and UTF-8
Hi all, Im try install postgres 9.0 on rhel and I try install cluster with 2 options of databases LATIN1 and UTF-8. It's possible? Paulo
Re: [GENERAL] Using regexp_replace to remove small words
Vick Khera vi...@khera.org writes: speaking of regular expressions... the other day I was trying to find where the 'flags' option to regexp_replace() is described, but I cannot find it in the 9.0 manual in any obvious place. it is not described in the string functions section, nor in the regexp section. It's described in the same paragraph that describes regexp_replace, in http://www.postgresql.org/docs/9.0/static/functions-matching.html The regexp_replace function provides substitution of new text for substrings that match POSIX regular expression patterns. It has the syntax regexp_replace(source, pattern, replacement [, flags ]). The source string is returned unchanged if there is no match to the pattern. If there is a match, the source string is returned with the replacement string substituted for the matching substring. The replacement string can contain \n, where n is 1 through 9, to indicate that the source substring matching the n'th parenthesized subexpression of the pattern should be inserted, and it can contain \ to indicate that the substring matching the entire pattern should be inserted. Write \\ if you need to put a literal backslash in the replacement text. (As always, remember to double backslashes written in literal constant strings, assuming escape string syntax is used.) The flags parameter is an optional text string containing zero or more single-letter flags that change the function's behavior. Flag i specifies case-insensitive matching, while flag g specifies replacement of each matching substring rather than only the first one. Other supported flags are described in Table 9-19. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: RES: [GENERAL] Using regexp_replace to remove small words
On 2010-12-13 13.24, Henrique de Lima Trindade wrote: Hi Peter, Your example works perfectly. But, I need Your help with on another situation. We're trying to create a plpgsql function with the expression. But, I'm getting a syntax error: - create or replace function sp_remove_small_words( ptext text ) returns text immutable as $$ begin return regexp_replace( ptext, $$\y\w{1,3}\y$$, '', 'g' ); end; $$ language plpgsql ; - ERRO: erro de sintaxe em ou próximo a \ LINE 6: return regexp_replace( ptext, $$\y\w{1,3}\y$$, '', 'g' ); You're ending the function declaration with the $$. Using $fun$ as For example, the following works: create or replace function sp_remove_small_words( ptext text ) returns text immutable as $fun$ begin return regexp_replace( ptext, $$\y\w{1,3}\y$$, '', 'g' ); end; $fun$ language plpgsql; For details on dollar quoting see: http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING Regards, roppert ^ ** Error ** ERRO: erro de sintaxe em ou próximo a \ SQL state: 42601 Character: 138 Thanks again! -Mensagem original- De: Peter Eisentraut [mailto:pete...@gmx.net] Enviada em: sexta-feira, 10 de dezembro de 2010 20:59 Para: Henrique de Lima Trindade Cc: pgsql-general@postgresql.org Assunto: Re: [GENERAL] Using regexp_replace to remove small words On fre, 2010-12-10 at 10:47 -0200, Henrique de Lima Trindade wrote: I'm trying to find a regular expression that removes all small (length N) words from a string. But, until now I've not been successful. Here is a start: select regexp_replace('Tommy Lee Jones', $$\y\w{2,3}\y$$, ' ', 'g' ); If you want to normalize the spaces after the removal and handle beginning and end of the word, you will need to expand this to cover those cases, but the example should contain the key ingredients. -- Regards, Robert roppert Gravsjö -- 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] Using regexp_replace to remove small words
On Mon, Dec 13, 2010 at 10:00 AM, Tom Lane t...@sss.pgh.pa.us wrote: Flag i specifies case-insensitive matching, while flag g specifies replacement of each matching substring rather than only the first one. Other supported flags are described in Table 9-19. Thanks. Quite well hidden in plain sight. :( As a user, I was looking for a table similar to 9-19, since I discounted it because it described the embedded flags. Based on the above text though it seems they are also valid for the flags parameter. -- 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] Do we want SYNONYMS?
On Dec 13, 2010, at 12:03 PM, Jasen Betts wrote: On 2010-12-07, Andy Colson a...@squeakycode.net wrote: I think it covers parts. In both you can create an alias to a table, both of which you can fire off insert/update/delete. I assume in PG you could have different permissions for the table and the alias, which I assume you can do in oracle. If we pretend oracle and PG both have the same thing as a schema, and using PG's definition of schema: I assume in oracle you can create table synonym schemaA.bob for schemaB.tablex And I assume you could do the same in PG. However beyond that, I dont know what oracle supports that we'd need. They want synonyms for functions, but as far as I can see the same can be achieved with minimal extra work by creating a new LANGUAGE SQL function that calls the original. CREATE FUNCTION newschema.newname( atype ... ) RETURNS rtype AS ' select oldschema.oldname ( $1 ... ) ' LANGUAGE SQL; with apropriare values for the lowercase bits and elipsis. This could possibly lead to performance issues , and there would be no error or warning message if you occasionally drop the oldschema.oldname, rendering the newschema.newname useless. /A -- Alexey Klyukin http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc -- 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] Do we want SYNONYMS?
On Dec 7, 2010, at 2:10 AM, Tom Lane wrote: Andy Colson a...@squeakycode.net writes: Can someone post what the synonyms will do? And what will be synonym'able? (cuz JD said: SYNONYMS work for things that aren't a table. then tgl said: synonyms for non-table things was pretty much rejected. Well, to clarify: what was shot down IMO was the proposed implementation with a separate catalog, which would have to be added to the lookup rules for every kind of object, in particular complicating the resolution rules for overloaded operators/functions even more than they already are. The simple fallback that we discussed was adding another relkind to pg_class entries, so that you could have a pg_class row that was just a reference to another one. That wouldn't introduce any new lookup complexity, because the synonym entry would be just like others (in particular, you couldn't have a synonym with exactly the same schema name + relname as some other pg_class row, so it adds no new ambiguity). But it would only provide synonyms for denizens of pg_class, ie, tables, views, sequences, indexes. Now, if there's really interest in synonyms for functions and so on, you could imagine extending the definitions of other system catalogs such as pg_proc to similarly allow alias entries in them. But it'd be a significant amount of work for each object type you wanted synonyms for, so you'd need to provide a convincing use-case for each one. So far, the plausible use-cases I've heard were just for tables, and maybe sequences. There's no data to share in a function. Agreed. I was also thinking about using catalog-specific changes to add synonyms for objects other than tables, views and sequences. It also possible that there's no need in synonyms for tables, but synonyms for table columns, or database roles would be useful. Hence, the question is not only 'do we want synonyms', but also, if we do, then for which kinds of objects? In any case, references to remote objects such as Oracle can do seem like an entirely separate issue. I'd prefer to avoid the Oracle terminology, if only to avoid confusion with that feature. Agreed as well. -- Alexey Klyukin http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Devart ADO.NET Data Providers Deliver Advanced Model-First and Database-First Support and Improved ORM Solution!
New versions of Devart ADO.NET Data Providers with improved capabilities of ORM solution and advanced Model-First and Database-First support in ORM model designer. Devart has recently announced the release of the new versions of dotConnect Data Providers, the database connectivity solutions built over ADO.NET architecture and development frameworks with a number of innovative technologies. Devart ADO.NET Data Providers product line includes high performance data providers for major databases and offer a complete solution for developing database-related applications and web sites. Devart dotConnect provides support for ADO.NET Entity Framework and LinqConnect. LinqConnect is the Devart ORM solution, closely compatible with the Microsoft LINQ to SQL, while extending its functionality with its own features. With new versions of dotConnect ADO.NET Data providers - dotConnect for Oracle 6.00, dotConnect for MySQL 6.00, dotConnect for PostgreSQL 5.00, and dotConnect for SQLite 3.00 – Devart continues to introduce innovative approaches for ORM model designing and considerably improves the runtime of LinqConnect. 1. New versions of dotConnects contains new greatly improved ORM solution - LinqConnect The functionality of LinqConnect ORM was extended by the following: Now LinqConnect supports Batch Update for DML statements execution on SubmitChanges call, which improves performance of update operations. New kind of inheritance hierarchy was supported. In addition to Table Per Hierarchy support Table per Type is supported now. LinqConnect package now includes the new templates for ASP.NET Dynamic Data projects, that allows creation ASP.NET Dynamic Data applications using LinqConnect. Upgraded LinqConnect ORM supports Parallel LINQ, which offers performance improvements for your applications, using LinqConnect. Visual Studio Debugger Visualizer was added to LinqConnect to improve readability of SQL code, generated for your LINQ queries, while debugging your application. 2. Enhanced own ORM model designer - Entity Developer Entity Developer, that is containing in dotConnect Data Providers, allows developing LINQ to SQL and Entity Framework models based on different database engines. Now it provides advanced Model First support and Update model from database functionality: Model-First approach implementation in Entity Developer offers automatic synchronization of mapping and storage part with the conceptual part of the model and easy to use wizards for synchronizing database with the model. Synchronizing Database with the Model Entity Developer offers you two options for creating database based on your model. You may either generate a create script, that creates a new database, or use Update to Database Wizard that synchronizes an existing database with the model. This wizard makes only necessary changes to the database and retain the data if possible. Even changes inside entity are detected and resulted in ALTER statements, the table doesn't need to be dropped and created again. The wizard displays the tree of the changed objects and their changes, allowing you to choose which database objects need to be synchronized with the model, specify the renamed objects, etc. Mapping Synchronization When automatic mapping synchronization is enabled, changes to the conceptual model are automatically applied to its mapping and storage part. For example, when you add a class to the conceptual part, a corresponding table is created in the storage part, and the class is automatically mapped to this table. Even the most complex conceptual part changes are supported - complex types, many-to-many associations, complex hierarchies. All these changes can be automatically reflected in the storage part. However you may make manual changes to the storage part, and they will be preserved when editing conceptual part. Update From Database Wizard allows synchronizing your model with the database for Entity Framework and LinqConnect in a fast and convenient way. Unlike standard Visual Studio Update Wizard, Entity Developer Update From Database Wizard tries to preserve manual changes to the model where possible. The wizard detects all the database changes that can affect the model, e.g. created and deleted tables and views, their columns and foreign keys, column datatype changes, created and deleted stored procedures and functions, changes to their parameters etc. Pricing and Availability We offer a free Express edition for each product from the dotConnect product line. A Single license price starts from as little as $99.95, and you can always choose the edition that matches your needs best. To learn more, download trial and free editions or order a license please visit Devart site: www.devart.com/dotconnect/ About Devart Devart is a software development company with 11 years of experience on the software market and over 20 thousands of devoted users. We specialize in providing comprehensive development and
Re: [GENERAL] Devart ADO.NET Data Providers Deliver Advanced Model-First and Database-First Support and Improved ORM Solution!
On Mon, Dec 13, 2010 at 6:27 AM, Devart sub...@devart.com wrote: With new versions of dotConnect ADO.NET Data providers - dotConnect for Oracle 6.00, dotConnect for MySQL 6.00, dotConnect for PostgreSQL 5.00, and dotConnect for SQLite 3.00 – It's too bad that you don't support modern versions of Oracle and Postgresql. :) -- Regards, Richard Broersma Jr. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Res: [ADMIN] Cluster with LATIN1 and UTF-8
I dont find way to create cluster... with example: psql -l teste1 |admin| LATIN1 teste2 | postgres | SQL_ASCII teste3| admin2| UTF8 -bash-3.1$ psql --version psql (PostgreSQL) 8.3.5 on this version in another server, it's working.. but I cant repeat on postgres 9 . Paulo De: Gabriele Bartolini gabriele.bartol...@2ndquadrant.it Para: paulo matadr saddon...@yahoo.com.br Cc: admin pgsql-ad...@postgresql.org Enviadas: Segunda-feira, 13 de Dezembro de 2010 13:11:20 Assunto: Re: [ADMIN] Cluster with LATIN1 and UTF-8 Ciao Paulo, Il 13/12/10 14:22, paulo matadr ha scritto: Im try install postgres 9.0 on rhel and I try install cluster with 2 options of databases LATIN1 and UTF-8. It's possible? Yes it is. I suggest that you look at the ENCODING option for the CREATE DATABASE command: http://www.postgresql.org/docs/9.0/interactive/sql-createdatabase.html Hope this helps. Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it
Re: [GENERAL] Problem while pg_xlog directory
Am Montag 13 Dezember 2010, um 14:47:03 schrieb Adarsh Sharma: mv pg_xlog pg_xlog_old But me server fail to start and there is empty log in startup.log. I also Hi,, you have to move the contents from the old to the new pg_xlog directory as well. HTH, Jens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Defining a Connection String in Windows XP
I would like to learn how to set a connection string. I am running pgsql 9.0 on an XP. I found a list of connection string examples: » dotConnect for PostgreSQL (former Core Labs PostgreSQLDirect) (PgSqlConnection) » Npgsql (NpgsqlConnection) » PostgreSQL OLE DB Provider » .NET Framework Data Provider for OLE DB (OleDbConnection) » PostgreSQL ODBC Driver (psqlODBC) » .NET Framework Data Provider for ODBC (OdbcConnection) I am trying to connect with SQL Maestro. Since pgsql provides an ODBC driver, it seemed appropriate to work with the PostgreSQL ODBC Driver. The suggested connection string is: Driver={PostgreSQL};Server=IP address;Port=5432;Database=myDataBase;Uid=myUsername;Pwd=myPassword; I do not know if I should leave the {PostgreSQL} as is or should it be replaced with something. For Server, the connection is on the same machine so I would think the value should be ‘localhost’ (without quotes). For Database, I don’t know if this should be the name of a database inside of my pgsql server or something else. When I run: Driver={PostgreSQL};Server=localhost;Port=5432;Database=myDataBase;Uid=user101;Pwd=pw101; I get a message window that says “[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified”. The driver is supposed to be inside of a pgsql DLL but I don’t know how Windows is supposed to find it. How should I set up the connection string? ray -- 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] Defining a Connection String in Windows XP
On 13/12/2010 18:46, ray wrote: I do not know if I should leave the {PostgreSQL} as is or should it be replaced with something. For Server, the connection is on the same machine so I would think the value should be ‘localhost’ (without quotes). Yes, that's right. For Database, I don’t know if this should be the name of a database inside of my pgsql server or something else. Yes, it's the specific database to which you want to connect. When I run: Driver={PostgreSQL};Server=localhost;Port=5432;Database=myDataBase;Uid=user101;Pwd=pw101; I get a message window that says “[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified”. What you're doing above is creating a DSN-less connection. I haven't done it that way, but have had no problems when creating a DSN first. Go to Administrative Tools - Data sources (ODBC) and create a data source for the database. You can then use a connection string something like this: dsn=yourDsnName;uid=myUsername;pwd=myPassword; HTH, Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Defining a Connection String in Windows XP
On Dec 13, 1:15 pm, r...@iol.ie (Raymond O'Donnell) wrote: On 13/12/2010 18:46, ray wrote: I do not know if I should leave the {PostgreSQL} as is or should it be replaced with something. For Server, the connection is on the same machine so I would think the value should be localhost (without quotes). Yes, that's right. For Database, I don t know if this should be the name of a database inside of my pgsql server or something else. Yes, it's the specific database to which you want to connect. When I run: Driver={PostgreSQL};Server=localhost;Port=5432;Database=myDataBase;Uid=user101;Pwd=pw101; I get a message window that says [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified . What you're doing above is creating a DSN-less connection. I haven't done it that way, but have had no problems when creating a DSN first. Go to Administrative Tools - Data sources (ODBC) and create a data source for the database. You can then use a connection string something like this: dsn=yourDsnName;uid=myUsername;pwd=myPassword; HTH, Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Ray, Thank you for responding. OK, that was my first trip into DSN setup and I got lost: The first step is to choose and existing MS driver for an Office app or 'Add' one from this 'User DSN' tab. The other tabs are: System dSN, File DSN, Drivers, Tracing, and Connection Pooling. I did not see a pgsql listed in any - what is the next step (or two)? ray -- 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] Defining a Connection String in Windows XP
On 13/12/2010 19:30, ray wrote: Thank you for responding. OK, that was my first trip into DSN setup and I got lost: The first step is to choose and existing MS driver for an Office app or 'Add' one from this 'User DSN' tab. The other tabs are: System dSN, File DSN, Drivers, Tracing, and Connection Pooling. I did not see a pgsql listed in any - what is the next step (or two)? Hi Ray, If you don't see the PostgreSQL driver listed under Drivers, then it hasn't been installed properly. How did you install it? You usually need to run the downloadable installer: http://www.postgresql.org/ftp/odbc/versions/msi Once the driver is installed, you create a DSN of the type of your choice (system, user or file) by going to the appropriate tab and click Add. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Defining a Connection String in Windows XP
Ray, Thank you for responding. OK, that was my first trip into DSN setup and I got lost: The first step is to choose and existing MS driver for an Office app or 'Add' one from this 'User DSN' tab. The other tabs are: System dSN, File DSN, Drivers, Tracing, and Connection Pooling. I did not see a pgsql listed in any - what is the next step (or two)? ray I usually use a System DSN. If, when you click Add, you don't see 'PostgreSQL ANSI' as one of the choices, you don't have the ODBC driver installed. If you do, just fill in the boxes with the database name, password, etc. Susan C. -- 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] Defining a Connection String in Windows XP
-Original Message- From: Susan Cassidy [mailto:scass...@stbernard.com] Sent: Monday, December 13, 2010 2:06 PM To: ray; pgsql-general@postgresql.org Subject: RE: [GENERAL] Defining a Connection String in Windows XP Ray, Thank you for responding. OK, that was my first trip into DSN setup and I got lost: The first step is to choose and existing MS driver for an Office app or 'Add' one from this 'User DSN' tab. The other tabs are: System dSN, File DSN, Drivers, Tracing, and Connection Pooling. I did not see a pgsql listed in any - what is the next step (or two)? ray I usually use a System DSN. If, when you click Add, you don't see 'PostgreSQL ANSI' as one of the choices, you don't have the ODBC driver installed. If you do, just fill in the boxes with the database name, password, etc. Susan C. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general This is great! Just for reference, the bat file would not run because there was not an existing pgsql driver. The msi file worked right off. I used a System DSN and it worked. Now to figure out what to do with it. BTW, what are some of the considerations for choosing DSN types System, File, User? Thank you very much, ray -- 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] Defining a Connection String in Windows XP
On 12/13/10 6:32 PM, ray joseph wrote: I used a System DSN and it worked. Now to figure out what to do with it. BTW, what are some of the considerations for choosing DSN types System, File, User? all three do the same thing in the end, they specify a ODBC database connection. the system DSN is available to all users of that system. the file DSN is just that, a file, that contains a few lines of text with the DSN information, you can bundle it with your app, and you reference it by the fully qualified path to the file. a user DSN is only available to the user that defined it. for some dorky reason, I don't think you can save the database password with these ad it has to be specified when you use the connection for various reasons, system DSN's are generally easier to use in microsoft-land. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Bytea error in PostgreSQL 9.0
I have program work with bytea, this field store image. Program work well in postgresql 8.3.9 but error in postgresql 9.0 Here is code to write image to database FileStream srcStream = new FileStream(file_name, FileMode.Open, FileAccess.Read); byte[] arrImage = new byte[srcStream.Length]; int read = srcStream.Read(arrImage, 0, arrImage.Length); string sql = INSERT INTO hrnvpict(ma_nv,pict) VALUES(@ma_nhan_vien ,@arrImage); Npgsql.NpgsqlConnection c = Public.cn; Npgsql.NpgsqlCommand comm = new NpgsqlCommand(sql, c); comm.Parameters.Add(new NpgsqlParameter(@arrImage, DbType.Binary)).Value = arrImage; comm.Parameters.Add(new NpgsqlParameter(@ma_nhan_vien, DbType.String, 40)).Value = _ma_nv; comm.ExecuteNonQuery(); And Here is code to read image from database string cmd = select pict from hrnvpict where trim(ma_nv)= ' + _ma_nv + '; Npgsql.NpgsqlConnection c = Public.cn; Npgsql.NpgsqlCommand comm = new NpgsqlCommand(cmd, c); Byte[] result = (Byte[])comm.ExecuteScalar(); MemoryStream pic = new MemoryStream(result); pictureBox1.Image = Image.FromStream(pic); //- 9.0 error here parameter is not valid My postgresql 8.3 install is made by msi download from www.postgresql.org. Postgresql 9 install is made by EnterpriseDB, it has LC_COLLATE = 'English_United States.1252' and LC_CTYPE = 'English_United States.1252' (In 8.3 I cannot found this) How to fix this. Please help me. Sorry for my English. Tuan Hoang Anh
[GENERAL] Bytea error in PostgreSQL 9.0
I have program work with bytea, this field store image. Program work well in postgresql 8.3.9 but error in postgresql 9.0 Here is code to write image to database FileStream srcStream = new FileStream(file_name, FileMode.Open, FileAccess.Read); byte[] arrImage = new byte[srcStream.Length]; int read = srcStream.Read(arrImage, 0, arrImage.Length); string sql = INSERT INTO hrnvpict(ma_nv,pict) VALUES(@ma_nhan_vien ,@arrImage); Npgsql.NpgsqlConnection c = Public.cn; Npgsql.NpgsqlCommand comm = new NpgsqlCommand(sql, c); comm.Parameters.Add(new NpgsqlParameter(@arrImage, DbType.Binary)).Value = arrImage; comm.Parameters.Add(new NpgsqlParameter(@ma_nhan_ vien, DbType.String, 40)).Value = _ma_nv; comm.ExecuteNonQuery(); And Here is code to read image from database string cmd = select pict from hrnvpict where trim(ma_nv)= ' + _ma_nv + '; Npgsql.NpgsqlConnection c = Public.cn; Npgsql.NpgsqlCommand comm = new NpgsqlCommand(cmd, c); Byte[] result = (Byte[])comm.ExecuteScalar(); MemoryStream pic = new MemoryStream(result); pictureBox1.Image = Image.FromStream(pic); //- 9.0 error here parameter is not valid My postgresql 8.3 install is made by msi download from www.postgresql.org. Postgresql 9 install is made by EnterpriseDB, it has LC_COLLATE = 'English_United States.1252' and LC_CTYPE = 'English_United States.1252' (In 8.3 I cannot found this) How to fix this. Please help me. Sorry for my English. Tuan Hoang Anh
Re: [GENERAL] Bytea error in PostgreSQL 9.0
On Mon, 13 Dec 2010 23:06:32 -0600, tuanhoanganh hatua...@gmail.com wrote: I have program work with bytea, this field store image. Program work well in postgresql 8.3.9 but error in postgresql 9.0 I don't know if this is your problem, but bytea changed in Postgres 9.0. Could you try enabling set bytea_output = escape? Regards, Mark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgresql 9 devel rpm
Can anyone tell me please where I can get the postgresql 9 devel rpms?
Re: [GENERAL] postgresql 9 devel rpm
On Tue, 2010-12-14 at 12:38 +0600, AI Rumman wrote: Can anyone tell me please where I can get the postgresql 9 devel rpms? http://yum.pgrpms.org or, directly: http://yum.pgrpms.org/9.0/ Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
[GENERAL] crosstab function
postgres 8.2.17 I am trying out the crosstab function (tablefunc contrib) for reporting needs and I'm having a problem. I have customers and products and the data is the quantity purchased. I am grouping by customername, productname in the source sql. My category sql depends if I want the products or customers to be the columns. When I make customers the rows and products the columns, it works fine. But when I make customers the columns and products the rows, there are duplicate product rows. Is there a way to group the product rows so that the data results come back correct? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Loading different files
Dear all, I am looking for a mechanism to load a specific file into an existing database. The file is a CTM (Compact Topic Maps Syntax ) file and I have written a CTM parser in plpgsql which works fine. The main problem is, that I have to load the file into memory and sent a query to database which causes an memory error because of a hugh file size. Is it possible to copy data from a file into database by using own functions? Thanks for response. -- 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] Loading different files
On 12/13/10 11:25 PM, Sven Krosse wrote: Dear all, I am looking for a mechanism to load a specific file into an existing database. The file is a CTM (Compact Topic Maps Syntax ) file and I have written a CTM parser in plpgsql which works fine. The main problem is, that I have to load the file into memory and sent a query to database which causes an memory error because of a hugh file size. Is it possible to copy data from a file into database by using own functions? you could use the Large Object API to stream the data into postgres. but that just stores it as a large blob of data. if you need more structured storage, I don't know what to suggest, except maybe parsing your CTM in your application and sending the data to postgres a row at a time. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general