Re: [GENERAL] Proper relational database?
On Sun, Apr 24, 2016 at 2:56 PM, wrote:
>> One of the people involved in that was Hugh Darwen, who is one of the authors
>> of The Third Manifesto, which is an attempt to define what a properly
>> relational language and system should look like. So you could say the
>> experience of ISBL vs SQL has been folded into that effort.
>
> See http://www.thethirdmanifesto.com/.
So what incremental improvements could we steal from "properly
relational" query languages?
Here's one I've thought about, trivial as it may be. I noticed that
Tutorial D (and apparently Andl too) includes a [NOT] MATCHING
operator (alternatively spelled SEMIJOIN and SEMIMINUS) corresponding
to the ⋉ (semi-join) and ▷ (anti-join) operators from relational
algebra. In SQL you write [NOT] EXISTS or [NOT] IN in the WHERE
clause, rather than something explicit in a clause,
though experienced users often talk explicitly about semi- and
anti-joins, both because of the theory and because the terms show up
in query plans.
A recent blog post[1] argues that SQL should have a SQL92-style JOIN
syntax for this and points at a couple of products that have added
one[2][3]. I guess it might be hard to convince the Postgres
community to add support for a non-standard syntax that doesn't give
you anything you can't already do, but as an idea I find it
interesting and it seems to be in the spirit of the part of the Third
Manifesto that says: "support[ing] the usual operators of the
relational algebra [..]. All such operators shall be expressible
without excessive circumlocution."
For example, say we want all students who have one or more exam today:
SELECT s.student_id, s.name
FROM student s
WHERE EXISTS (SELECT 1
FROM exam e
WHERE e.student_id = s.student_id
AND e.exam_date = CURRENT_DATE)
I don't know Tutorial D, but I think it might express that with something like:
( student MATCHING exam
WHERE exam_date = CURRENT_DATE )
{ student_id, name }
With 10 minutes of prototype hacking I convinced Postgres to accept
SEMI and ANTI like this:
SELECT s.student_id, s.name
FROM student s SEMI JOIN exam e USING (student_id)
WHERE e.exam_date = CURRENT_DATE
I guess a real version should accept (or require?) LEFT or RIGHT
before SEMI/ANTI. When using this hypothetical syntax I think you
should be allowed to refer to e.exam_date in the WHERE clause but not
in the SELECT list (Impala apparently does allow you to see data from
exam, and returns values from an arbitrary matching row, but that
doesn't seem right to me). But the EXISTS syntax is correspondingly
strange in that it requires you to provide a SELECT list which is
entirely discarded, so people often write "*" or "1" (erm, OK, I guess
you can use an empty select list in recent Postgres).
[1]
https://blog.jooq.org/2015/10/13/semi-join-and-anti-join-should-have-its-own-syntax-in-sql/
[2]
http://www.cloudera.com/documentation/archive/impala/2-x/2-0-x/topics/impala_joins.html
[3] https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins
--
Thomas Munro
http://www.enterprisedb.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] Is it possible to recover the schema from the raw files?
Hello Venkata, Thank you for your reply. You are stating the obvious though. If those conditions were met, I would have formulated my question differently. Kind regards, Tomas -- 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] Proper relational database?
On 24 April 2016 at 08:36, Thomas Munro wrote: > For example, say we want all students who have one or more exam today: > > SELECT s.student_id, s.name > FROM student s >WHERE EXISTS (SELECT 1 >FROM exam e > WHERE e.student_id = s.student_id > AND e.exam_date = CURRENT_DATE) To be fair to SQL a far simpler version would be SELECT DISTINCT student_id, name FROM student INNER JOIN exam USING(student_id) WHERE exam_date=CURRENT_DATE; To find students with no exam today (the other point of your argument): SELECT student_id, name FROM student LEFT JOIN exam USING(student_id) WHERE exam_date=CURRENT_DATE AND exam.student_id IS NULL; Not really sure what the issue is with either of those. Once learned, they're both very easy and straightforward. Geoff -- 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] Proper relational database?
> ow...@postgresql.org] On Behalf Of Thomas Munro
> So what incremental improvements could we steal from "properly relational"
> query languages?
Here is my list of deficiencies in some or all dialects of SQL.
-Relation and tuple as data types
-Globally exclude NULLs and duplicate columns
-Relation with no columns, null key
-Natural antijoin
-Tuple join operations
-Tuple tests for equality, superset, subset
-Tuple test for set membership of relation
-Relation tests for equality, superset, subset
-Relation set operations include symmetric difference
-User-defined functions of arbitrary complexity
-User-defined aggregation functions of arbitrary complexity
-User-defined ordered queries of arbitrary complexity
-Iterative/recursive queries (when)
> Here's one I've thought about, trivial as it may be. I noticed that Tutorial
> D (and apparently Andl too) includes a [NOT] MATCHING operator (alternatively
> spelled SEMIJOIN and SEMIMINUS) corresponding to the ⋉ (semi-join) and ▷
> (anti-join) operators from relational algebra. In SQL you write [NOT] EXISTS
> or [NOT] IN in the WHERE clause, rather than something explicit in a table> clause, though experienced users often talk explicitly about semi- and
> anti-joins, both because of the theory and because the terms show up in query
> plans.
Yes, there are two joins (join and antijoin). Semijoin is just one of a number
of projections following a join, but antijoin is a quite different algorithm.
Antijoin is quite hard to write in SQL in such a way that the query planner
will do the right thing. There is a lot of variation between dialects.
> A recent blog post[1] argues that SQL should have a SQL92-style JOIN syntax
> for this and points at a couple of products that have added one[2][3]. I
> guess it might be hard to convince the Postgres community to add support for
> a non-standard syntax that doesn't give you anything you can't already do,
> but as an idea I find it interesting and it seems to be in the spirit of the
> part of the Third Manifesto that says: "support[ing] the usual operators of
> the relational algebra [..]. All such operators shall be expressible without
> excessive circumlocution."
The purpose is simply that explicit syntax allows for explicit query
optimisation.
>
> For example, say we want all students who have one or more exam today:
>
> SELECT s.student_id, s.name
> FROM student s
>WHERE EXISTS (SELECT 1
>FROM exam e
> WHERE e.student_id = s.student_id
> AND e.exam_date = CURRENT_DATE)
>
> I don't know Tutorial D, but I think it might express that with something
> like:
>
> ( student MATCHING exam
> WHERE exam_date = CURRENT_DATE )
> { student_id, name }
>
> With 10 minutes of prototype hacking I convinced Postgres to accept SEMI and
> ANTI like this:
>
> SELECT s.student_id, s.name
> FROM student s SEMI JOIN exam e USING (student_id)
>WHERE e.exam_date = CURRENT_DATE
>
> I guess a real version should accept (or require?) LEFT or RIGHT before
> SEMIANTI.
Andl has semijoin, rsemijoin, ajoin and rajoin. They are trivial to add once
you have the basic algorithm, but Tutorial D does not.
When using this hypothetical syntax I think you should be allowed
> to refer to e.exam_date in the WHERE clause but not in the SELECT list
> (Impala apparently does allow you to see data from exam, and returns values
> from an arbitrary matching row, but that doesn't seem right to me). But the
> EXISTS syntax is correspondingly strange in that it requires you to provide a
> SELECT list which is entirely discarded, so people often write "*" or "1"
> (erm, OK, I guess you can use an empty select list in recent Postgres).
SQL has an implicit ordering of query evaluation -- you will often need to
write a nested subquery or correlated query for what should be very
straightforward situations. That's another thing that's easy to fix, if allowed.
Regards
David M Bennett FACS
Andl - A New Database Language - andl.org
--
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] Proper relational database?
On 24 April 2016 at 12:29, Geoff Winkless wrote: > To find students with no exam today (the other point of your argument): > > SELECT student_id, name > FROM student > LEFT JOIN exam USING(student_id) > WHERE exam_date=CURRENT_DATE AND exam.student_id IS NULL; *sigh* problem with writing emails in a rush. Of course I meant SELECT student.student_id, name FROM student LEFT JOIN exam ON exam.student_id=student.student_id AND exam_date=CURRENT_DATE WHERE exam.student_id IS NULL; Geoff -- 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] Is it possible to recover the schema from the raw files?
On 04/24/2016 03:54 AM, Tomas J Stehlik wrote: Hello Venkata, Thank you for your reply. You are stating the obvious though. If those conditions were met, I would have formulated my question differently. So to be clear, all that remains of the database cluster are the files you copied out from the data directory, correct? In your original post you said: "The attempt to connect to one of the databases results in multiple errors, like the following one: ..." That would imply that the server actually started, is that the case? What happened if you connected to another database in the cluster? Kind regards, Tomas -- Adrian Klaver adrian.kla...@aklaver.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] Is it possible to recover the schema from the raw files?
Hello Adrian, > So to be clear, all that remains of the database cluster are the files > you copied out from the data directory, correct? Yes and no. For the purpose of this discussion, it may be better to say "yes" though. > That would imply that the server actually started, is that the case? Yes. The database server's files were not damaged. > What happened if you connected to another database in the cluster? That's irrelevant. That said, some were damaged and some not. Kind regards, Tomas -- 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] Is it possible to recover the schema from the raw files?
On Sun, Apr 24, 2016 at 03:00:09PM +0100, Tomas J Stehlik wrote: > > What happened if you connected to another database in the cluster? > > That's irrelevant. I dare assume Adrian asked for a reason :-) Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Is it possible to recover the schema from the raw files?
On 04/24/2016 07:09 AM, Karsten Hilbert wrote: On Sun, Apr 24, 2016 at 03:00:09PM +0100, Tomas J Stehlik wrote: What happened if you connected to another database in the cluster? That's irrelevant. I dare assume Adrian asked for a reason :-) Yes, the reason being that the OP was asking about the possibility of recovering schema information, not data. Given a server that starts and a database to connect to in the cluster then it might be possible to get that information without resorting to mining the raw files(something I do not know how to do anyway). This of course assumes that the system tables where not corrupted. As a test, if you can connect to a database in the cluster what happens if you do?: select * from pg_class; or if you want to cut to the chase: pg_dump -s -d database_in_question -h some_host -U some_user Karsten -- Adrian Klaver adrian.kla...@aklaver.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] Is it possible to recover the schema from the raw files?
Please note that I mentioned previously that the database is corrupt. "pg_class" table can be queried but it is not possible to dump the database in question as some of the pages in blocks are missing. In this case, it is necessary to rely on raw files only. Thanks. T -- 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] Is it possible to recover the schema from the raw files?
On Sun, Apr 24, 2016 at 11:16 AM, Tomas J Stehlik wrote: > Please note that I mentioned previously that the database is corrupt. > > "pg_class" table can be queried but it is not possible to dump the database > in question as some of the pages in blocks are missing. > > In this case, it is necessary to rely on raw files only. Thanks. > > T > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > Please note, in cases of this kind, it is always pertinent to provide the PostgreSQL version and O/S, especially if the possibility exists it might be related to an existing bug. >The raw files were backed up separately outside of the original data folder upon discovery of the issue. That's not going to help you, as the files you need are already corrupted at that point. If, however, you have a backup of the raw files _prior_ to the crash, you might be in luck. So, since you verified it is only one database that is the problem, but the PostgreSQL server can access the others, do the following: SELECT oid, datname FROM pg_database WHERE datname = 'your_bad_db_name'; The oid is the directory file under the base directory that needs to be restored. That is the directory (and all sub files) that needs to be restored. First, stop the PostgreSQL server. Back up that current (but bad) directory and all sub files. Then restore the good backup of that directory only! Restart the PostgreSQL server and hopefully you will then have access to an old version of the corrupted database. If successful, immediately take a SQL dump of that database. *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] Is it possible to recover the schema from the raw files?
On 04/24/2016 08:16 AM, Tomas J Stehlik wrote: Please note that I mentioned previously that the database is corrupt. "pg_class" table can be queried but it is not possible to dump the database in question as some of the pages in blocks are missing. So did you do a schema only dump or a complete dump? In this case, it is necessary to rely on raw files only. Thanks. Well if the corrupted raw files include the system information then I think you are out of luck. T -- Adrian Klaver adrian.kla...@aklaver.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] Is it possible to recover the schema from the raw files?
On Sun, Apr 24, 2016 at 04:16:10PM +0100, Tomas J Stehlik wrote: > Please note that I mentioned previously that the database is corrupt. Given the facts that Adrian attempted to engage in a solution-bound conversation all the while mentioning that he doesn't know how to recover the schema from the raw files I feel inclined to consider it a fair assumption that he did, indeed, note that you mentioned that the database is corrupt. :-) > "pg_class" table can be queried but it is not possible to dump the database > in question as some of the pages in blocks are missing. If I recall correctly, you stated that the data isn't important in this case. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Is it possible to recover the schema from the raw files?
Hello Melvin, Thank you. Your reply is similar to the first one in that it makes assumptions to the contrary of my original e-mail. Therefore I shall tackle only the relevant bits: 1. This is not related to any existing bug. It was a corruption of the file system. 2. OID of the corrupt database was known previously. 3. If I had raw files from before the corruption, I wouldn’t have written to this mailing list. 4. There is nothing to restore, as we are not talking about backups. The original question was whether is it possible to *recover* the schema from the raw files? Kind regards, Tomas -- 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] Is it possible to recover the schema from the raw files?
Hello Adrian, Thank you. > So did you do a schema only dump or a complete dump? There is no dump. There are just raw files. > Well if the corrupted raw files include the system information > then I think you are out of luck. Well, this topic is not about "luck". The question potentially targets someone who could tell whether something like this is possible. Kind regards, Tomas -- 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] Is it possible to recover the schema from the raw files?
Hello Karsten, > If I recall correctly, you stated that the data isn't > important in this Yes, exactly. However, pages in blocks apparently store also the representations of the database schemas. And those are also corrupt. A tiny bit only but still corrupt. Therefore the overall information is *largely* intact - yet the small missing amount is causing PostgreSQL not being able to work with this particular database. Kind regards, Tomas -- 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] Is it possible to recover the schema from the raw files?
On Sun, Apr 24, 2016 at 04:35:56PM +0100, Tomas J Stehlik wrote: > > So did you do a schema only dump or a complete dump? > > There is no dump. There are just raw files. I believe Adrian wanted to know whether you attempted a schema or complete dump *after* the fact, like what he suggested a mail ago or so. In case the FS corruption "only" affects raw files related to user data (as opposed to also affecting data in pg_* tables) a schema-only dump does have a slight chance of success. That chance might potentially be increased by judicious use of zero_damaged_pages and related low-level techniques the prerequisite conditions of which people seem to have been trying to inquire about upthread. > The question potentially targets someone who could tell whether something > like this is possible. "possible" depends no the exact circumstances, the details of which people have been trying to tease out. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Is it possible to recover the schema from the raw files?
On Sun, Apr 24, 2016 at 04:35:56PM +0100, Tomas J Stehlik wrote: >> Well if the corrupted raw files include the system information >> then I think you are out of luck. > > Well, this topic is not about "luck". Surely, English isn't my vernacular language - but "out of luck" has seemed to be an euphemism for "no, it ain't possible" to me unto now. Adrian cautiously added "I think" (as in 'he thinks'). Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Is it possible to recover the schema from the raw files?
On 04/24/2016 08:35 AM, Tomas J Stehlik wrote: Hello Adrian, Thank you. So did you do a schema only dump or a complete dump? There is no dump. There are just raw files. Have you even tried a schema only dump from the original instance? Well if the corrupted raw files include the system information > then I think you are out of luck. Well, this topic is not about "luck". The question potentially targets someone who could tell whether something like this is possible. If the information is not there, it is not there. In other words if the zeroed out blocks you alluded to cover the system information then I am not sure how it would be possible to recover information from 0? Kind regards, Tomas -- Adrian Klaver adrian.kla...@aklaver.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] Is it possible to recover the schema from the raw files?
On 04/24/2016 08:54 AM, Karsten Hilbert wrote: On Sun, Apr 24, 2016 at 04:35:56PM +0100, Tomas J Stehlik wrote: Well if the corrupted raw files include the system information then I think you are out of luck. Well, this topic is not about "luck". Surely, English isn't my vernacular language - but "out of luck" has seemed to be an euphemism for "no, it ain't possible" to me unto now. Adrian cautiously added "I think" (as in 'he thinks'). Yeah, should have been smarter in my word choice. Karsten -- Adrian Klaver adrian.kla...@aklaver.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] Is it possible to recover the schema from the raw files?
Hello Karsten, Thank you. > I believe Adrian wanted to know whether you attempted a > schema or complete dump *after* the fact, like what he > suggested a mail ago or so. > > In case the FS corruption "only" affects raw files related to > user data (as opposed to also affecting data in pg_* tables) > a schema-only dump does have a slight chance of success. > > That chance might potentially be increased by judicious use > of zero_damaged_pages and related low-level techniques the > prerequisite conditions of which people seem to have been > trying to inquire about upthread. > > > The question potentially targets someone who could tell > > whether something like this is possible. > > "possible" depends no the exact circumstances, the details of > which people have been trying to tease out. All this relevant information has already been supplied previously. Kind regards, Tomas -- 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] Is it possible to recover the schema from the raw files?
On Sun, Apr 24, 2016 at 04:42:21PM +0100, Tomas J Stehlik wrote: >> If I recall correctly, you stated that the data isn't >> important in this > > Yes, exactly. However, pages in blocks apparently store also the > representations of the database schemas. And those are also corrupt. In that case _my_ knowledge also goes only so far as to be able to fear "no it is not possible". More knowledgeable people may - given more detailed information - still be able to suggest approaches to recover most if not all of the schema. Like replacing (some of) the pg_* containing raw files with those from an uncorrupted database (having been suggested earlier this year) which may work if the corrupted blocks in pg_* only affect data actually describing _that_ database rather than establishing relationships not unique to this database (say, encodings, default operators, ...). If those can be replaced and there is still corruption in some parts describing the local schema then it may work to apply zero_damaged_pages, pg_resetxlog, and similar tools in order to make some of the schema dumpable. It may help to look into disabling system indexe as well. Best regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Is it possible to recover the schema from the raw files?
Hello Andrian, Thank you > Have you even tried a schema only dump from the original instance? That's an odd question. However, I understand that maybe a lot of beginners come onto this mailing list. To the contrary, this is a non-trivial situation. You can safely assume that I wrote the original request because all the other approaches failed to bring a result. Everything has already been tried and tested. The only question remains the one in the subject line. > If the information is not there, it is not there. In other words if the > zeroed out blocks you alluded to cover the system information then I am > not sure how it would be possible to recover information from 0? This is an incorrect assumption. Most of the information is available. Just the PostgreSQL server can't work with it [because of the missing bits]. Kind regards, Tomas -- 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] Is it possible to recover the schema from the raw files?
On Sun, Apr 24, 2016 at 04:58:48PM +0100, Tomas J Stehlik wrote: >>> The question potentially targets someone who could tell >>> whether something like this is possible. >> >> "possible" depends no the exact circumstances, the details of >> which people have been trying to tease out. > > All this relevant information has already been supplied previously. In that case I must surely have missed it and feel I can no longer be of any assistance, even if it only amounted to but contributing leads to be investigated. Sorry. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is it possible to recover the schema from the raw files?
On Sunday, April 24, 2016, Karsten Hilbert > wrote: > On Sun, Apr 24, 2016 at 04:35:56PM +0100, Tomas J Stehlik wrote: > > >> Well if the corrupted raw files include the system information > >> then I think you are out of luck. > > > > Well, this topic is not about "luck". > > Surely, English isn't my vernacular language - but "out of > luck" has seemed to be an euphemism for "no, it > ain't possible" to me unto now. > > Adrian cautiously added "I think" (as in 'he thinks'). > > Not quite. It means the outcome does rely on luck to some degree. You got lucky if the area of corruption doesn't include the area in which the data you want resides. You were unlucky, or out of luck, if the corruption includes the desired data. It's still an exaggeration because the luck being noted is quite specific within this specific context the point of luck playing a factor is correctly made. My understanding is the potential exists but the outcome is uncertain. People are suggesting some of the simple ways to accomplish this goal before informing you like quite possibly you should consider hiring someone specializing in this sort of thing. You should be understanding that some things may have been tried or already deemed insufficient and do it anyway - it's part of a process which itself i difficult to do over email. You might trying hooking up with someone on IRC... David J.
Re: [GENERAL] Is it possible to recover the schema from the raw files?
Hello Karsten, Thank you. > Like replacing (some of) the pg_* containing raw files with > those from an uncorrupted database (having been suggested > earlier this year) which may work if the corrupted blocks in > pg_* only affect data actually describing _that_ database > rather than establishing relationships not unique to this > database (say, encodings, default operators, ...). If those > can be replaced and there is still corruption in some parts > describing the local schema then it may work to apply > zero_damaged_pages, pg_resetxlog, and similar tools in order > to make some of the schema dumpable. This is actually a very interesting idea. Kind regards, Tomas -- 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] Is it possible to recover the schema from the raw files?
Hello Karsten, > In that case I must surely have missed it and feel I > can no longer be of any assistance, even if it only > amounted to but contributing leads to be investigated. > Sorry. No problem at all. I very much appreciate all your input and ideas. Thank you. Kindest regards, Tomas -- 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] Is it possible to recover the schema from the raw files?
On 04/24/2016 09:07 AM, Tomas J Stehlik wrote: Hello Andrian, Thank you Have you even tried a schema only dump from the original instance? That's an odd question. However, I understand that maybe a lot of beginners come onto this mailing list. To the contrary, this is a non-trivial situation. You can safely assume that I wrote the original request because all the other approaches failed to bring a result. Everything has already been tried and tested. The only question remains the one in the subject line. If the information is not there, it is not there. In other words if the > zeroed out blocks you alluded to cover the system information then I am > not sure how it would be possible to recover information from 0? This is an incorrect assumption. Most of the information is available. Just the PostgreSQL server can't work with it [because of the missing bits]. Then I am of no further use to this conversation. Kind regards, Tomas -- Adrian Klaver adrian.kla...@aklaver.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] Is it possible to recover the schema from the raw files?
Hello Adrian, > Then I am of no further use to this conversation. No problem at all. Thank you for your well considered input and ideas. Have a lovely day. Kindest regards, Tomas -- 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] Is it possible to recover the schema from the raw files?
On Sun, Apr 24, 2016 at 05:07:10PM +0100, Tomas J Stehlik wrote: >> Have you even tried a schema only dump from the original instance? > > That's an odd question. However, I understand that maybe a lot of beginners > come onto this mailing list. :-) > You can safely assume that I wrote the original request because all the > other approaches failed to bring a result. Everything has already been tried > and tested. Given the fact that no-one will know "all" approaches to "everything" it may help to bring together which approaches have actually been tried in which exact fashion. Personally, I'd be humble enough to assume (hope ?) someone would know Everything+1. In which case I'd rush to supply Everything in order to most quickly learn of +1. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Background worker plus language handler for Andl: OK?
It seems to me that it should be possible to create a language front end for Andl by creating a background worker and a language handler. The BGW will need to implement the Andl language and a listener, and submit generated SQL to SPI. The PL will get called by query functions and pass them to the BGW (via a callback) for execution. AFAIK the BGW and the PL run in the same process, and this should work. Apart from reading the documentation (I have), reading source code (worker_spi.c) and heeding warnings about the risk of breaking the server, is there any reason why this would not work? There is a fair bit of work, and it would be nice to know what to watch out for. Regards David M Bennett FACS Andl - A New Database Language - andl.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
