18

In SEDE the PostHistory table holds for PostHistoryTypeId's 33 and 34 the Notice added and Notice removed events.

When inspecting those rows with this query the comment field holds a number.

creationdate        |Post Link           |posthistorytypeid   |comment
---------------------------------------------------------------------
2015-03-29 3:24     |28687143            |34                  |82288
2015-03-29 2:49     |27269748            |34                  |81888
2015-03-29 2:49     |29116980            |34                  |81892
2015-03-29 2:49     |29134963            |34                  |81889
2015-03-29 2:49     |29135107            |34                  |81891
2015-03-29 2:49     |27269748            |34                  |81888
2015-03-29 2:49     |29116980            |34                  |81892
2015-03-29 2:49     |29134963            |34                  |81889
2015-03-29 2:49     |29135107            |34                  |81891

Does that number represents a foreign-key to a table that is missing from SEDE?

If yes, and not a great secret, can that table be added to SEDE?
If no, what does that number represent?

A similar request to add the notice texts in the UI is here

yes, this question was inspired by this bountied question

Improve this question
6
  • I think a little more compelling is focusing on a specific post. For example (and this is just selected by random from your query output), see this query for post 3870088. There are a total of 1042 post history entries of type 33 & 34, all of which have the same Comment ID. However, there's not reference anywhere of what these history changes are. They occurred around the time of the bounty issue and awarding, but no idea why so many... – Werner Apr 2 '15 at 23:14
  • Well, the notice-added 33 vote and notice-removed 34 vote associated with a given notice seem to always both have the same value in the Comment column. I guess it's some sort of unique notice ID? If the Comment column weren't there, I think it would be impossible in some cases to determine which 33 vote corresponded to which 34 vote. (e.g. a moderator both notice-ing and bounty-ing a single post at around the same time) – senshin Apr 3 '15 at 0:53
  • @Werner That's just side-effect of the join to Votes, the restricting WHERE clause was removed in the query you linked – Tim Stone Apr 3 '15 at 2:13
  • 1
    The number corresponds to the preselected notice text, AFAIK. That data is currently missing from SEDE, like close reasons. – Tim Stone Apr 3 '15 at 2:18
  • 1
    @TimStone I don't think that can be the case. Each distinct value of Comment appears at most twice (once with PostHistoryTypeId = 33, once with = 34): data.stackexchange.com/stackoverflow/query/296205. And surely the stock notice texts have been used more than once on SO. – senshin Apr 3 '15 at 23:31
  • 1
    @senshin Probably because (I'd guess) a new, unique, notice comment is created per question in the foreign table. I'd guess that either the text is stored in full in whatever that table is, or that table also includes a reference to some other set of notice text. In any case it's probably some "notices" table or something, with dates, owner user id, and all that, just like the current "comments" table but for notices, and not present in sede for whatever reason. – Jason C Apr 5 '15 at 22:48
14

Update: February 13, 2017

As of the latest release of the Data Explorer, the PostNotices and PostNoticeTypes tables are now available to query.

I quickly tweaked your original query to this one and you can see the PostNotice details available.

As mentioned in the Database schema documentation the PostHistory has the Comment column. This column can contain a variety of different things which are foreign keys to other tables.

For PostHistoryTypeId's 33 and 34, the Comment column contains the associated Id to a table not in SEDE. That table has Post Notice details including PostId, CreationDate, OwnerUserId, PostNoticeTypeId, etc.

As far as it being added to SEDE, I'm not sure if there was a reason for it to be excluded in the first place, so I'll this to see if we can add it.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .